DAX Tutorial: How to Count Things That Did Not Happen?

We already know how to analyze things that happened because for every occurrence we have a record in the fact table that tracks that event. What do we do to analyze things that did not happen? For example, what if I am looking at my sales data and trying to figure out what products are not selling, what customer did not purchase what product in a given month?

Those questions are much harder to answer because we do not have a record for transactions that did not occur. Turns out that previous statement is not quite true, there is a simple calculation that we can implement to gain insight into those types of questions.

You can download the Powe BI Desktop model here.

this is the calculation that we us in the video:

# of Products with No Sales = 
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            VALUES('Product'[ProductID]), 
            "Sales Amount", 
            0+[Sales $]
        ), 
        [Sales Amount] = 0
    )
)