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 ROLLUPGROUP function and ROLLUPADDISSUBTOTAL 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
-- ADDMISSINGITEMS adds rows that would be skipped by SUMMARIZECOLUMNS -- when the value of all the measures is blank. -- In the example, years 2005, 2006 and 2010 and 2011 are added back by -- ADDMISSINGITEMS in the second result. -- SUMMARIZECOLUMNS in the first query does not return them, because there -- were no sales. EVALUATE SUMMARIZECOLUMNS ( 'Date'[Calendar Year], "Amt", [Sales Amount] ) EVALUATE ADDMISSINGITEMS ( 'Date'[Calendar Year], SUMMARIZECOLUMNS ( 'Date'[Calendar Year], "Amt", [Sales Amount] ), 'Date'[Calendar Year] ) ORDER BY [Calendar Year] ASC
Calendar Year | Amt |
---|---|
2007-01-01 | 11,309,946.12 |
2008-01-01 | 9,927,582.99 |
2009-01-01 | 9,353,814.87 |
Calendar Year | Amt |
---|---|
2005-01-01 | (Blank) |
2006-01-01 | (Blank) |
2007-01-01 | 11,309,946.12 |
2008-01-01 | 9,927,582.99 |
2009-01-01 | 9,353,814.87 |
2010-01-01 | (Blank) |
2011-01-01 | (Blank) |
-- With ADDMISSINGITEMS you specify the columns to add (first set) -- and the column used to group by (second set) -- In the example, even though the grouping happens by year and month -- ADDMISSINGITEMS adds back only the year rows EVALUATE ADDMISSINGITEMS ( 'Date'[Calendar Year], SUMMARIZECOLUMNS ( 'Date'[Calendar Year], 'Date'[Calendar Year Month], "Amt", [Sales Amount] ), 'Date'[Calendar Year] )
-- You can also specify table filters applied to the filter context -- while retrieving the missing items. -- In this example, ADDMISSINGITEMS only returns 2010 and 2011, -- because of the last filter that removes 2005 and 2006 EVALUATE ADDMISSINGITEMS ( 'Date'[Calendar Year], SUMMARIZECOLUMNS ( 'Date'[Calendar Year], "Amt", [Sales Amount] ), 'Date'[Calendar Year], EXCEPT ( ALL ( 'Date'[Calendar Year] ), { "CY 2005", "CY 2006" } ) )
Calendar Year | Amt |
---|---|
CY 2007 | 11,309,946.12 |
CY 2008 | 9,927,582.99 |
CY 2009 | 9,353,814.87 |
CY 2010 | (Blank) |
CY 2011 | (Blank) |
Related functions
Other related functions are:
Last update: Dec 4, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Jocelyn Saw, Pedro Gravanita, Kenneth Barber
Microsoft documentation: https://docs.microsoft.com/en-us/dax/addmissingitems-function-dax