How does this DAX Code work for non buying customers

[Martin_R]

Hi,

I'm currently learning DAX using book the definitive guide to DAX. In the book there is a measure NonBuyingCustomers that doesn't make sense to me. Could someone explain how the variable CustomersWithoutSales is working ?

My understanding is that this variable should return a list of CustomerKey where there is no keyvalue in related table Sales. How can this then use the list of customers with sales from sales table to check ISEMPTY ( RELATEDTABLE ( Sales ) ) ?

Coming from a SQL background I would write a query that LEFT JOINS Customers to Sales and returns those Customers without rows in Sales Table for a given ProductName

NonBuyingCustomers =

VAR SelectedCustomers =

    CALCULATETABLE (

        DISTINCT ( Sales[CustomerKey] ),

        ALLSELECTED ()

    )

VAR CustomersWithoutSales =

    FILTER (

        SelectedCustomers,

        ISEMPTY ( RELATEDTABLE ( Sales ) )

    )

VAR Result =

    COUNTROWS ( CustomersWithoutSales )

RETURN

    Result

https://drive.google.com/file/d/1ZiIgqO4aHb87mh5wrJRjDpzZzSnZQCAn/view?usp=drive_link 

[DataInsights · Accepted solution]

@Martin_R,

There's an error in the DAX. The variable SelectedCustomers should use Customer[CustomerKey]. It was noted in the errata (at one point all the errors were available but I'm unable to locate that list now). The link below is mentioned on page xix of the book.

https://www.microsoftpressstore.com/store/definitive-guide-to-dax-business-intelligence-with-9781509... 

The logic is as follows:

1. Get a distinct list of all CustomerKey in the Customer dimension table, using the filter context outside the visual (i.e. filters and slicers).

2. Filter the list in step 1 to include only CustomerKey not in the Sales fact table.

3. Count the rows in step 2.

[Martin_R]

Thank you Data Insights. I found the errata page here.

https://www.sqlbi.com/books/the-definitive-guide-to-dax-2nd-edition/companion/