ADDMISSINGITEMS DAX Function (Table manipulation)

Add the rows with empty measure values back.

Syntax

ADDMISSINGITEMS ( [<ShowAll_ColumnName> [, <ShowAll_ColumnName> [, … ] ] ], <Table> [, <GroupBy_ColumnName> [, [<FilterTable>] [, <GroupBy_ColumnName> [, [<FilterTable>] [, … ] ] ] ] ] ] )

Parameter	Attributes	Description
ShowAll_ColumnName	Optional Repeatable	ShowAll columns.
Table		A SummarizeColumns table.
GroupBy_ColumnName	Optional Repeatable	A column to group by or a call to ROLLUP function and ISSUBTOTALCOLUMNS function to specify a list of columns to group by with subtotals.
FilterTable	Optional Repeatable	An expression that defines the table from which rows are to be returned.

Return values

Table An entire table or a table with one or more columns.

Examples

Consider the result of the following query with SUMMARIZECOLUMNS:

EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Calendar Year],
    "Quantity", SUM ( Sales[Quantity] )
)

Calendar Year	Quantity
CY 2007	5,551,636
CY 2008	5,029,924
CY 2009	6,956,456

The Date table includes years ranging from 2005 to 2011, but SUMMARIZECOLUMNS only shows years that have a result in Quantity. You can include years without any corresponding quantity by wrapping the expression in ADDMISSINGITEMS, specifying the columns for which you want to include “empty” members. The next query adds the years without data to the result of SUMMARIZECOLUMNS:

EVALUATE
ADDMISSINGITEMS (
    'Date'[Calendar Year],
    SUMMARIZECOLUMNS (
        'Date'[Calendar Year],
        "Quantity", SUM ( Sales[Quantity] )
    ),
    'Date'[Calendar Year]
)

Calendar Year	Quantity
CY 2007	5,551,636
CY 2008	5,029,924
CY 2009	6,956,456
CY 2005
CY 2006
CY 2010
CY 2011

Last update: Aug 7, 2020 » Contribute » Show contributors

Contributors: Alberto Ferrari, Marco Russo

MSDN documentation: https://docs.microsoft.com/en-us/dax/addmissingitems-function-dax