ADDMISSINGITEMS 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]…)


, [ROLLUPISSUBTOTAL(][, ][, ][, ]…[)], [, filterTable]…)


ShowAllColumn – A column for which to return items with no data for the measures used.

Table – A table containing all items with data (NON EMPTY) for the measures used.

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

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

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.

Return Value

Reference Data

DAX Functions / Filter Functions / ADDMISSINGITEMS

Date and Time Functions Filter Functions Financial Functions Information Functions
Logical Functions Math and Trig Functions Other Functions Parent and Child Functions
Statistical Functions Text Functions Time Intelligence Functions

Power Query M Functions

Accessing Data Functions Binary Functions Combiner Functions Comparer Functions Date functions DateTimeZone functions
Duration Functions Error handling Functions Expression Functions Function Values List Functions Lines Functions
Logical Functions Number Functions Record Functions Replacer Functions Splitter Functions Table functions
Text Functions Time functions Type functions Uri functions Value functions