ADDMISSINGITEMS

A grouped table with a row added, its other columns blank, for each combination of values it lacks.

Group: Table manipulation. Compatible with DAX.

Syntax

ADDMISSINGITEMS ( <showAll_columnName> [, <showAll_columnName>] …, <table>
    [, <groupBy_columnName>] … [, <filterTable>] … )

Parameter	Type	Description
`showAll_columnName`	Column repeatable	A column whose every value should appear, with data or without. Several may follow one another.
`table`	Table	The grouped table to complete, usually what `SUMMARIZECOLUMNS` returned.
`groupBy_columnName`	Column optional, repeatable	A grouping column of that table, named again: the same columns as the show-all ones, as DAX expects them. For a summary with subtotals, `ROLLUPISSUBTOTAL` with the column and its subtotal flag.
`filterTable`	Table optional, repeatable	A filter the summary had, so that values it removed do not come back as missing.

Returns

Table. The table's own rows, then a row for every missing combination of the show-all values, blank in its other columns.

Remarks

SUMMARIZECOLUMNS drops a row when all its expressions are blank. ADDMISSINGITEMS puts those rows back; it is how a visual shows items with no data.
The combinations come from the model: columns of one table combine as that table holds them together, columns of different tables in every way.
Pass the summary's filters again as filter tables. Without them, values the filters had removed reappear as missing items.
The added rows are blank in every other column, the measures too.
A summary with subtotals from ROLLUPADDISSUBTOTAL is completed through ROLLUPISSUBTOTAL in place of the grouping columns: the missing items are added on each level, with their subtotal flags filled in (example 3).

Examples

Every kind of place, sold or not

The depot sells no beer, so SUMMARIZECOLUMNS alone gives only the shops' row.

EVALUATE
ADDMISSINGITEMS (
    Places[Kind],
    SUMMARIZECOLUMNS ( Places[Kind], "Liters", [Liters sold] ),
    Places[Kind]
)
ORDER BY Places[Kind]

Places[Kind]	Liters
Depot	(blank)
Shop	1806463

Missing items within a filter

Only IPA and Pils had shop months over 2,000 liters. Lettøl is a beer and comes back blank; Cider is outside the filter and stays out.

EVALUATE
ADDMISSINGITEMS (
    Products[Product],
    SUMMARIZECOLUMNS (
        Products[Product],
        FILTER ( ALL ( Products[Category] ), Products[Category] = "Beer" ),
        FILTER ( ALL ( Sales[Liters] ), Sales[Liters] > 2000 ),
        "Shop months", COUNTROWS ( Sales )
    ),
    Products[Product],
    FILTER ( ALL ( Products[Category] ), Products[Category] = "Beer" )
)
ORDER BY Products[Product]

Products[Product]	Shop months
IPA	9
Lettøl	(blank)
Pils	252

Missing items in a summary with a total

The form Power BI writes for a table with a total row: the products without such months come back blank, and the total keeps its flag.

EVALUATE
ADDMISSINGITEMS (
    Products[Product],
    SUMMARIZECOLUMNS (
        ROLLUPADDISSUBTOTAL ( Products[Product], "Is total" ),
        FILTER ( ALL ( Sales[Liters] ), Sales[Liters] > 2000 ),
        "Shop months", COUNTROWS ( Sales )
    ),
    ROLLUPISSUBTOTAL ( Products[Product], [Is total] )
)
ORDER BY [Is total], Products[Product]

Products[Product]	Is total	Shop months
Cider	FALSE	(blank)
IPA	FALSE	9
Lettøl	FALSE	(blank)
Pils	FALSE	252
(blank)	TRUE	261

SUMMARIZECOLUMNS, ROLLUPISSUBTOTAL, SUMMARIZE, CROSSJOIN.

Last updated 3 November 2026 · OpenBI Studio 0.3.74