ADDMISSINGITEMS Function (DAX)

How does the ADDMISSINGITEMS function (DAX) work?

The ADDMISSINGITEMS function (DAX) adds combinations of items from different columns to a table if they do not already have it yet.  

ADDMISSINGITEMS Formula Syntax

ADDMISSINGITEMS(
    <showALLColumn>[,<showAllColumn>]…,<table>,<groupingColumn>[,<groupingColumn>]…[FilterTable]…
)

How do you use the ADDMISSINGITEMS function?

Determining which item to add to a table will depend on referencing source columns which contain all the possible values. 

The ADDMISSINGITEMS function will also return BLANK values for every IsSubtotal column of blank rows it adds. 

Related Blog Posts

Related Support Forum Posts

Grouped Summary
Summarize with filtered measure
Removing Context From SUMMARIZE

Considerations when using the ADDMISSINGITEMS function

To come up with the item combinations from different columns, AutoExist will need to be utilized for the columns within the same table, while CrossJoin is used for the columns within the different tables. 

Related Video Tutorials

Formula examples using the ADDMISSINGITEMS function

= VAR ‘RowHeadersShowAll’
= CALCULATETABLE (
ADDMISSINGITEMS (
[Sales Territory Country], [Sales Territory Region],        ‘RowHeadersInCrossTab’,
ROLLUPISSUBTOTAL (
[Sales Territory Group], [Subtotal for Sales Territory Group], [Sales Territory Country], [Subtotal for Sales Territory Country], [Sales Territory Region], [Subtotal for Sales Territory Region] ),
‘RowHeaders’ ),
‘DateFilter’,’TerritoryFilter’
)

EVALUATE
ADDMISSINGITEMS ( ‘Date'[Calendar Year],
SUMMARIZECOLUMNS (‘Date'[Calendar Year], “Quantity”, SUM ( Sales[Quantity] ) ),
‘Date'[Calendar Year] )

Related Course Modules

DOWNLOAD


Download our comprehensive DAX Formula Reference Guide as perfect companion as you learn how to use DAX formulas within Power BI.


Download DAX Formula Reference Guide