ADDMISSINGITEMS

Applies to: Calculated column Calculated table Measure Visual calculation

Adds rows with empty values to a table returned by SUMMARIZECOLUMNS.

Syntax

ADDMISSINGITEMS ( [ [,  [, … ] ] ],  [,  [, [] [,  [, [] [, … ] ] ] ] ] ] )

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