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

[eriks]

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.

[MattAllington]

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

[eriks]

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]

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/