Chapter 13: Filter moving upstream?

A dedicated forum to help people when working through the book "Learn to Write DAX"
PowerLee
Posts: 2
Joined: Fri Jul 06, 2018 6:12 pm

Chapter 13: Filter moving upstream?

Postby PowerLee » Fri Jul 06, 2018 6:34 pm

Hello Matt,

I have a question regarding chapter 13, specifically at the section where you are writing: "Note: I have swapped the column Products[Category] with Territories[Country] above
to show an alternate view of the data. These measures work regardless of which column you have
on Rows on the pivot table."
Pag. 128

Why would that work if there is no relationship between the Territories table and the Customers table and filters only move downstream?
I understand that the customer table is being filtered and only customers whose [Total Sales] are >= 5,000 $ are taken thanks to Row Context turning into Filter Context.

The way I understand is: Customers table is being filtered and only customers with [Total Sales] >=5000 are left. Then for each row in the pivot table (for instance Australia), the Sales table is filtered and only Territory Keys associated with Australia are left. Then the filtered Sales table filters the previously filtered Customers table (the customer keys left in the Sales table when the Sales table is filtered by Australia are then used to filter the previously filtered Customers table). But this would imply the the filter is moving upstream from the Sales table to the Customers table.

I am used to the CROSSFILTER function to calculate things like the Number of Male Customers from Europe when I drop the Territories Group in the rows section of a pivot table



Image

PowerLee
Posts: 2
Joined: Fri Jul 06, 2018 6:12 pm

Re: Chapter 13: Filter moving upstream?

Postby PowerLee » Fri Jul 20, 2018 11:40 pm

Hello Matt, after thinking about it I got why this is working, the filter iterates through the Customers table and Context Transition happens, the Sales table becomes filtered by the customer on the current row and by the country in the pivot table, then only the rows on the Customer table where this expression is higher then 5000 are kept and the rows are counted

so where previously I was using CROSSFILTER, now I can use:



cool stuff, power pivot rocks around the clock


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

Who is online

Users browsing this forum: No registered users and 1 guest