Partners,,,, russianxnxx

Suppose you have a transaction table with products and sales, its easy to get sales by products, but what about the products that DID NOT SELL? It is not very straightforward to find unsold products. In this post, you will learn how to find such missing / unsold products using some DAX.


Find Missing Values (Products) in Power BI


Consider this Data Model

  • Sales and Products Tables are related: Product ID (Sales) <> Product Code (Products)
  • Sales and Calendar Tables are related: Date (Sales) <> Date (Calendar)


Using Products Table to Report Sales

Here, I do get total sales of products that were sold in each month, however, I don’t get to see the the UNSOLD products in each month

find missing products - total sales

To solve this we can use the existing Products dimension table

find missing products - products table

This table contains the list of all the products available to sell. As a simple reconciliation check we can see this table against the sales of products each month and find which products are not present. However, this a laborious task if done manually. So we use DAX!


Find Missing Products using DAX

I am going to talk about 2 approaches here: CONCATENATEX Approach & COUNTROWS Approach. The crux of both the approaches is that

We compare the list of sold products with the products dimension table (which contains all products) to see which products didn’t sell.



Missing Products =
VAR SoldProducts = VALUES(Sales[Product ID])
VAR ProductsAvailable = ALL(Products[Product Code])
        [Total Sales USD],
            EXCEPT(ProductsAvailable, SoldProducts),
            Products[Product Code],


  1. We created 2 variables that store ProductsAvailable list and SoldProducts list.
  2. EXCEPT function returns a list that has all values from list 1 that are not there in list 2.
  3. UNICHAR(10) – new line – is used as a delimiter.

Here’s the output. Sweet!

find missing products - concatenatex output

The drawback with this approach is that if we have a long list of unsold SKUs, say 100, the concatenated list will become quite lengthy and difficult to comprehend. To solve this we use the COUNTROWS Approach and display a cross ❌ against each unsold product.


#2 COUNTROWS Measure:

The best way to visualize a long list of the missing products would be to have a Missing Products column that marks ❌ in case the product is unsold.

find missing products - output

Missing Product =
VAR AllProd = VALUES(Products[Product Code])
VAR SoldProd = VALUES(Sales[Product ID])
        COUNTROWS(EXCEPT(AllProd, SoldProd))

find missing products - missing products dax

  1. We created 2 variables AllProd and SoldProd.
  2. These variables store lists of unique Product Codes/ID from Product and Sales Tables respectively considering the filter context.
  3. The SELECTEDVALUE is a Boolean column in the Calendar Table which flags TRUE for dates which fall between the entire sales period.
  4. Just like before instead of concatenating multiple values, I instead use COUNTROWS(EXCEPT(AlProd, SoldProd)) to count the missing products.
SalesPresent = // this is column created in the calendar table
('Calendar'[Date] <= MAX(Sales[Date])) && 
'Calendar'[Date] >= (EOMONTH(MIN(Sales[Date]),-1) + 1)


One Last Thing – The above measure returns the count of the products, this can be changed to ❌ by simple conditional formatting to get the final result.


    ⬇️ Pop in your Name & Email to get the file!


    More on DAX:

    1. Optimizing IF using MAX and MIN Functions
    2. Switch Between Calendar and Fiscal Year – Power BI
    3. 3 Ways to Find Duplicate Values in Dimension Tables
    4. Related and RelatedTable in DAX
    5. Debugging DAX using CONCATENATEX


    Topics that I write about...