ADDMISSINGITEMS in DAX: Including Missing Categories in Query Results

What Is ADDMISSINGITEMS in DAX?

ADDMISSINGITEMS is used with table expressions such as SUMMARIZECOLUMNS to add back group-by items that are missing from the result because there are no corresponding rows in the current filter context.

This is particularly useful when you want a report or query to show the complete list of categories, products, dates, or other grouping items, even when some of them do not have data after filtering.

1. Grouping Sales by Product Category

Query

EVALUATE
    SUMMARIZECOLUMNS(
        'Product'[Category],
        "Total Sales", SUM(Sales[Sales Amount])
    )

Step-by-step explanation

Step 1: EVALUATE
EVALUATE returns the result of the table expression as a table.

Step 2: SUMMARIZECOLUMNS
SUMMARIZECOLUMNS creates a summarized result based on the grouping columns and calculations specified inside the function.

Step 3: ‘Product'[Category]
This is the grouping column. The query creates one result row for each product category that participates in the summarized result.

Step 4: “Total Sales”
This is the name of the calculated result column.

Step 5: SUM(Sales[Sales Amount])
For each category, DAX calculates the total of Sales[Sales Amount].

What will you get?

The result contains the product categories along with their corresponding Total Sales.

The categories and values above are illustrative. Your actual result depends on the data in the model.

2. Filtering the Query to Red Products

Query

EVALUATE
    SUMMARIZECOLUMNS(
        'Product'[Category],
        FILTER('Product', 'Product'[Color] = "Red"),
        "Total Sales", SUM(Sales[Sales Amount])
    )

Step-by-step explanation

Step 1: ‘Product'[Category]
The result is still grouped by product category.

Step 2: FILTER(‘Product’, ‘Product'[Color] = “Red”)
The FILTER function creates a filter over the Product table where the product color must be Red.

Step 3: Total Sales
The query calculates SUM(Sales[Sales Amount]) under the red-product filter context.

What changes?

The important difference is that the query is no longer considering all products. It is specifically evaluating the sales associated with products whose Color is Red.

If a category has no red products, or no sales associated with the filtered context, that category may not appear in the summarized result.

3. Adding Missing Categories with ADDMISSINGITEMS

Query

EVALUATE
    ADDMISSINGITEMS(
        'Product'[Category],
        SUMMARIZECOLUMNS(
            'Product'[Category],
            FILTER('Product', 'Product'[Color] = "Red"),
            "Total Sales", SUM(Sales[Sales Amount])
        ),
        'Product'[Category]
    )

Step-by-step explanation

Step 1: EVALUATE
The query returns the table produced by ADDMISSINGITEMS.

Step 2: ADDMISSINGITEMS
ADDMISSINGITEMS takes the summarized result and adds missing grouping items back into the result.

Step 3: ‘Product'[Category]
The first argument identifies the grouping column for which missing items should be considered.

Step 4: SUMMARIZECOLUMNS(…)
The second argument is the summarized table. It groups data by category, applies the red-product filter, and calculates total sales.

Step 5: ‘Product'[Category]
The final argument specifies the grouping column whose missing values should be added back.

Why do we need ADDMISSINGITEMS?

Consider a simple example. Suppose the Product table contains these categories:

After applying the filter Color = “Red”, the Clothing category may not have data to contribute to the summarized result.

Without ADDMISSINGITEMS, the summarized output can contain only the categories represented by the filtered result.

With ADDMISSINGITEMS, the missing category can be added back to the result, allowing the output to preserve the complete category grouping.

Understanding the Query Structure

ADDMISSINGITEMS(
    'Product'[Category],
    SUMMARIZECOLUMNS(
        'Product'[Category],
        FILTER('Product', 'Product'[Color] = "Red"),
        "Total Sales", SUM(Sales[Sales Amount])
    ),
    'Product'[Category]
)

1. First argument: identifies the grouping column used to add missing items.
2. Second argument: provides the summarized table generated by SUMMARIZECOLUMNS.
3. Third argument: specifies the group-by column for which missing items should be restored.

Comparing the Three Queries

Important Notes

1. Filtering can remove grouping items from the result

When a filter is applied, some categories may have no corresponding data in the filtered context. As a result, those categories may be absent from the summarized output.

2. ADDMISSINGITEMS does not create sales data

ADDMISSINGITEMS adds missing grouping items to the result. It does not manufacture sales transactions or create values that do not exist in the underlying data.

3. It is useful for complete category or dimension outputs

This technique can be useful when you want a query result to retain the complete set of grouping items even when some items do not have data under the current filter context.

4. ADDMISSINGITEMS works with summarized results

In this example, ADDMISSINGITEMS is applied around a SUMMARIZECOLUMNS expression. Understanding the summarized table first makes the behavior of ADDMISSINGITEMS much easier to follow.

EVALUATE
    ADDMISSINGITEMS(
        'Product'[Category],
        SUMMARIZECOLUMNS(
            'Product'[Category],
            FILTER('Product', 'Product'[Color] = "Red"),
            "Total Sales", SUM(Sales[Sales Amount])
        ),
        'Product'[Category]
    )

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist