5: Concept: Filter Propagation  [Solved]

A dedicated forum to help people when working through the book "Learn to Write DAX"
RafaelKnuth
Posts: 4
Joined: Fri May 18, 2018 8:07 pm

5: Concept: Filter Propagation

Postby RafaelKnuth » Mon May 21, 2018 7:44 pm

I am trying to wrap my head around filter context, and I am not sure I am there yet.

Here's an example of two different measures in column C and D respectively, which are supposed to get the same result, but they don't:

A.JPG
A.JPG (23.39 KiB) Viewed 1000 times


In column C I am using DISTINCTOUNT:

D.JPG
D.JPG (210.44 KiB) Viewed 1000 times


In Column D though, I am using COUNTROWS:

CR.JPG
CR.JPG (205.05 KiB) Viewed 1000 times


With COUNTROWS, I can only count the number of rows in the entire table.
DISTINCTCOUNT however calculates the number of rows in a column within a table.

My guess is that filters work in a hierarchical order in DAX and filters that are higher up in the hierarchy (table based) can overwrite those lower in the hierarchy (column based). Am I right?

RamanaV
Posts: 14
Joined: Thu Oct 19, 2017 12:57 pm

Re: 5: Concept: Filter Propagation  [Solved]

Postby RamanaV » Tue May 22, 2018 2:47 pm

Hi,

First, a clarification on COUNTROWS and DISTINCTCOUNT:
* COUNTROWS returns the total number of rows in the table.
So, your number of customers is the total number of rows in the Customers table.
* DISTINCTCOUNT returns the number of distinct (unique) values in the column in the table.
So, your formula returns the total number of customers who made purchases. Got it? Only those who had some purchases and hence are showing in Sales table.

Next, your pivot table:
1. In Rows you have Products[Category]. This is the Initial Filter Context.
2. In Values, firstly you have distinct count from Sales table. The initial filter from Products table flows down to Sales table, and gets applied to this measure. So, you get different answers for each product category.
3. In Values, secondly you have count rows. But this is on Customers table.
The filters flow downhill but they don't flow uphill. So, no filters applied on Customers table and you will get the total value in all rows of the pivot table.

To test this you replace Products[Category] on Rows with Customers[Occupation] and observe what happens.

RafaelKnuth
Posts: 4
Joined: Fri May 18, 2018 8:07 pm

Re: 5: Concept: Filter Propagation

Postby RafaelKnuth » Tue May 22, 2018 7:43 pm

Thank you so much for the clarification!


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

Who is online

Users browsing this forum: No registered users and 0 guests

cron