ADDMISSINGITEMS DAX

The ADDMISSINGITEMS DAX function adds rows with empty values to a table returned by SUMMARIZECOLUMNS.

Let’s understand how the ADDMISSINGITEMS DAX function works. In the dataset below, there are empty sales values for a few customer IDs. When using a SUMMARIZECOLUMNS table for calculations, it will return only the rows with existing values.

However, if you want to include customer IDs with no sales data, you can enclose the SUMMARIZECOLUMNS expression within the ADDMISSINGITEMS function. This ensures that rows with missing values are also included in the result.

Sales Dataset

Let’s start with the practical. Follow the steps below-

Step-1: Load the sales dataset above into Power BI.

Step-2: Next, create a new table by going to the Modeling tab and selecting ‘Write a DAX expression to create a new table’. See the screenshot below for reference.

Step-3: First, we will check the result of the SUMMARIZECOLUMNS DAX table. Write the DAX code below.

SummarizeColumnTable =
SUMMARIZECOLUMNS(
'Sales'[CustomerID],
"Total Sales", SUM ( Sales[Sales] )
)

As you can see from the above result, SUMMARIZECOLUMNS does not show the missing values.

Step-4: Now, write the DAX code to show the missing values in the SUMMARIZECOLUMNS result.

Add another table. Go to the Modeling tab and select ‘Write a DAX expression to create a new table.’ Then, write the DAX code below.

AddmissingItem table =
ADDMISSINGITEMS (
'Sales'[CustomerID],
SUMMARIZECOLUMNS(
'Sales'[CustomerID],
"Total Sales", SUM ( Sales[Sales] )
)
,
'Sales'[CustomerID]
)

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome.

Refer more DAX function – DAX Tutorials