DAX Function Guide

ADDMISSINGITEMS Function (DAX)
Empty image or helper icon

Sam McKay

CEO & Founder

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 Function (DAX) Formula Syntax

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

How do you use the ADDMISSINGITEMS Function (DAX)?

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

Loading

Considerations when using the ADDMISSINGITEMS Function (DAX)?

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

Loading

Formula examples using the ADDMISSINGITEMS Function (DAX)

= 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 Courses

Loading