ADDMISSINGITEMS
Applies to: Calculated column Calculated table Measure Visual calculation
Adds rows with empty values to a table returned by SUMMARIZECOLUMNS.
Syntax
ADDMISSINGITEMS ( [<showAll_columnName> [, <showAll_columnName> [, … ] ] ], <table> [, <groupBy_columnName> [, [<filterTable>] [, <groupBy_columnName> [, [<filterTable>] [, … ] ] ] ] ] ] )
Parameters
Term | Definition |
---|---|
showAll_columnName | (Optional) A column for which to return items with no data for the measures used. If not specified, all columns are returned. |
table | A SUMMARIZECOLUMNS table. |
groupBy_columnName | (Optional) A column to group by in the supplied table argument. |
filterTable | (Optional) A table expression that defines which rows are returned. |
Return value
A table with one or more columns.
Remarks
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
With SUMMARIZECOLUMNS
A table returned by SUMMARIZECOLUMNS will include only rows with values. By wrapping a SUMMARIZECOLUMNS expression within an ADDMISSINGITEMS expression, rows containing no values are also returned.
Example
Without ADDMISSINGITEMS, the following query:
SUMMARIZECOLUMNS(
'Sales'[CustomerId],
"Total Qty", SUM ( Sales[TotalQty] )
)
Returns,
CustomerId | TotalQty |
---|---|
A | 5 |
B | 3 |
C | 3 |
E | 2 |
With ADDMISSINGITEMS, the following query:
EVALUATE
ADDMISSINGITEMS (
'Sales'[CustomerId],
SUMMARIZECOLUMNS(
'Sales'[CustomerId],
"Total Qty", SUM ( Sales[TotalQty] )
),
'Sales'[CustomerId]
)
Returns,
CustomerId | TotalQty |
---|---|
A | 5 |
B | 3 |
C | 3 |
D | |
E | 2 |
F |