DAX Filter - ADDMISSINGITEMS function

Description

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.

DAX ADDMISSINGITEMS function is new in Excel 2016.

Syntax

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

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

Parameters

Sr.No. Parameter & Description
1

showAllColumn

A column for which to return items with no data for the calculated fields used.
2

table

A table containing all items with data (NON EMPTY) for the calculated fields used.
3

groupingColumn

A column which is used to group by in the supplied table argument.
4

isSubtotal_columnName

A Boolean column in the supplied table argument which contains ISSUBTOTAL values for the corresponding groupingColumn column.
5

filterTable

A 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.

Remarks

To determine the combinations of items from different columns to evaluate −

  • AutoExist is applied for columns within the same table.
  • CrossJoin is applied across different tables.

ADDMISSINGITEMS with ROLLUPGROUP

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

Restrictions

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

  • The ADDMISSINGITEMS function requires that, if ROLLUPISSUBTOTAL is 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 the 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 is 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.

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

Example

= ADDMISSINGITEMS (Products, FILTER (Products,Products[Product]="Air Purifier"))
dax_functions_filter.htm
Advertisements