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: Oct 20, 2018   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo

MSDN documentation: https://msdn.microsoft.com/en-us/query-bi/dax/addmissingitems-function-dax

DAX Conventions
Context Transition

This function performs a Context Transition if called in a Row Context. Click to read more.

Row Context

This expression is executed in a Row Context. Click to read more.

Not recommended

The use of this function is not recommended. See Remarks and Related functions for alternatives.

Not recommended

The use of this parameter is not recommended.

Deprecated

This function is deprecated. Jump to the Alternatives section to see the function to use.

Deprecated

This parameter is deprecated and its use is not recommended.

Contribute

Want to improve the content of ADDMISSINGITEMS? Did you find any issue? Please, report it us!
All submissions will be evaluated for possible updates of the content.