ADDMISSINGITEMS DAX Function (Table manipulation)
Add the rows with empty measure values back.
Syntax
| 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
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: Jan 11, 2021 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
MSDN documentation: https://docs.microsoft.com/en-us/dax/addmissingitems-function-dax