Why does this calculate work (and others don't)?

This is a dedicated forum for people that have questions about the book Supercharge Power BI. In the event of errors in the book, the error information will be in this forum.
eriks
Posts: 9
Joined: Mon Mar 09, 2020 10:56 pm

Why does this calculate work (and others don't)?

Postby eriks » Wed Apr 22, 2020 11:00 pm

I have a small question during the excercises in the book. I cannot figure out why if you use

Customers with Sales Greater Than 5000 = CALCULATE(COUNTROWS(FILTER(Customers;[Total Sales]>=5000)))

is is working well while you put countries on rows and it is not working for

Total Number of Customers = (countrows(Customers))

I understand that you filter customers based on a total sales filter. However, I don't understand why the first formula works while filtering on product category (on rows). In my opinion you if try to filter upstream (from customers to sales to products) it does not work.
Attachments
calculate (2).png
calculate (2).png (8.57 KiB) Viewed 2112 times

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

Re: Why does this calculate work (and others don't)?

Postby MattAllington » Sun Apr 26, 2020 7:10 am

I would not say “works” and “doesn’t work”. They both work, but the first in the visual doesn’t filter and the second does. As you have said, the key difference is the measure inside the formula. The measure receives the filter from the visual in 2 ways. Firstly, simply it is because the rows of a visual filter a measure; that’s how it works. But secondly, there is a more complex action. The filter function has a row context (chapter 10) and the total sales measure creates context transition. So the result is that the customers table is also filtering the result.

Total customers has no embedded measure and no filter path from products to customers
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

eriks
Posts: 9
Joined: Mon Mar 09, 2020 10:56 pm

Re: Why does this calculate work (and others don't)?

Postby eriks » Mon May 04, 2020 9:21 pm

Thanks for your answer. I understand now that you can filter the customers table based on context transition (only count customers > 5000 dollar). However, this measure is situated in the customers table. So basically you can filter a measure in a lookup table based on another look up table as e.g. country (the rows in the visual)?

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

Re: Why does this calculate work (and others don't)?

Postby MattAllington » Tue May 05, 2020 9:00 pm

Yes. Data tables can filter lookup table, but it doesn’t happen automatically. Read my article here
https://powerpivotpro.com/2014/08/filte ... s-that-is/
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training


Return to “Help with the Book "Supercharge Power BI"”

Who is online

Users browsing this forum: No registered users and 1 guest