ADDMISSINGITEMS Function (DAX)

 
System_CAPS_ICON_note.jpg Note


This function is included in SQL Server 2016 Analysis Services (SSAS), Power Pivot in Excel 2016, and Power BI Desktop nly. Information provided here is subject to change.

Adds combinations of items from multiple columns to a table if they do not already exist. The determination of which item combinations to add is based on referencing source columns which contain all the possible values for the columns.

To determine the combinations of items from different columns to evaluate: AutoExist is applied for columns within the same table while CrossJoin is applied across different tables.

The ADDMISSINGITEMS function will return BLANK values for the IsSubtotal columns of blank rows it adds.

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

ADDMISSINGITEMS(<showAllColumn>[, <showAllColumn>]…, <table>, [ROLLUPISSUBTOTAL(]<groupingColumn>[, <isSubtotal_columnName>][, <groupingColumn>][, <isSubtotal_columnName>]…[)], [, filterTable]…)  

Parameters

TermDefinition
showAllColumnA column for which to return items with no data for the measures used.
tableA table containing all items with data (NON EMPTY) for the measures used.
groupingColumnA column which is used to group by in the supplied table argument.
isSubtotal_columnNameA Boolean column in the supplied table argument which contains ISSUBTOTAL values for the corresponding groupingColumn column.
filterTableA table representing filters to include in the logic for determining whether to add specific combinations of items with no data. Used to avoid having ADDMISSINGITEMS add in item combinations which are not present because they were removed by a filter.

ROLLUPGROUP is used inside the ROLLUPISSUBTOTAL function to reflect ROLLUPGROUPs present in the supplied table argument.

Restrictions

  • If ROLLUPISSUBTOTAL was used to define the supplied table argument (or the equivalent rows and ISSUBTOTAL columns were added by some other means), ROLLUPISSUBTOTAL must be used with the same arguments within ADDMISSINGITEMS. This is also true for ROLLUPGROUP if it was used with ROLLUPISSUBTOTAL to define the supplied table argument.

  • The ADDMISSINGITEMS function requires that, if ROLLUPISSUBTOTAL was used to define the supplied table argument, ISSUBTOTAL columns corresponding to each group by column, or ROLLUPGROUP, are present in the supplied table argument. Also, the names of the ISSUBTOTAL columns must be supplied in the ROLLUPISSUBTOTAL function inside ADDMISSINGITEMS and they must match names of Boolean columns in the supplied table argument. This enables the ADDMISSINGITEMS function to identify BLANK values stemming from the fact that a row is a subtotal row from other BLANK values.

  • If ROLLUPGROUP was used with ROLLUPISSUBTOTAL to define the supplied table argument, exactly one ISSUBTOTAL column name must be supplied per ROLLUPGROUP and it must match the corresponding ISSUBTOTAL column name in the supplied table argument.

Add blank rows for columns with "show items with no data" turned on. The ADDMISSINGITEMS function will return NULLs/BLANKs for the IsSubtotal columns of blank rows it adds.

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'   
)  

Example with ROLLUPGROUP

VAR 'RowHeadersShowAll' =   
CALCULATETABLE   
(  
ADDMISSINGITEMS   
(  
[Sales Territory Country],     
[Sales Territory Region],   
'RowHeadersInCrossTab',   
ROLLUPISSUBTOTAL   
(  
ROLLUPGROUP    
(  
[Sales Territory Group],   
[Sales Territory Country]   
),   
[Subtotal for Sales Territory Country],   
[Sales Territory Region],   
[Subtotal for Sales Territory Region]   
),   
'RowHeaders'   
)  

Community Additions

ADD
Show: