## 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?

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: 929
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

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

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 Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/