Ch 5 how to fix Total Number of Customers 18,484?

A dedicated forum to help people when working through the book "Learn to Write DAX"
HelKR
Posts: 1
Joined: Thu May 19, 2016 11:53 am

Ch 5 how to fix Total Number of Customers 18,484?

Postby HelKR » Thu May 19, 2016 12:31 pm

I am on pg 51 - 56. This section describes Filter Propagation. I want to change the formula for Total Number of Customers to show count only those who bought a red product.

DAX formula given for Total Number of Customers =COUNTROWS(ALL(Products)). Since there is a 1-many relationship between Customer->Sales and Products->Sales I should be able to make the connection between how many customers bought a red Accessory, Bike, Clothing, and Components. But how?

How to change the DAX so that instead of 18, 484 all the way down the column, the numbers reflect number of customers that bought a red project in each category (Accessories, Bikes, Clothing, Components)?

MattAllington
Posts: 849
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Ch 5 how to fix Total Number of Customers 18,484?

Postby MattAllington » Thu May 19, 2016 6:06 pm

Hi. In excel this is a lot harder to understand than in Power BI Desktop. The short answer in Excel is to add the name of the sales table as a filter inside calculate

=calculate ( countrows(customers) ,sales)

For an explanation, read my blog post here http://exceleratorbi.com.au/many-many-r ... explained/
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax


Return to “Help with the Book "Learn to Write DAX"”

Who is online

Users browsing this forum: No registered users and 1 guest