DAX Topic FILTER() - Page 106 "Under the Hood"

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.
ed_at_wig
Posts: 3
Joined: Fri Nov 30, 2018 1:22 am

DAX Topic FILTER() - Page 106 "Under the Hood"

Hello, I may have misunderstood what the expected result should be.

the formula:

Total Customers with Income of \$80,000 Under the Hood =
CALCULATE (
COUNTROWS ( Customers ),
FILTER ( ALL ( Customers ), Customers[YearlyIncome] >= 80000 )
)

returned the same value (4,382) on each row of the matrix within the filter context of Occupation.

I was expecting it to work like the formula listed above it in the book:

Total Customers with Income of \$80,000 or above 2 =
CALCULATE ( COUNTROWS ( Customers ), Customers[YearlyIncome] >= 80000 )

which returns the correct value on each row of the matrix within the filter context of Occupation.

Any clarification on the "Under the Hood" formula is greatly appreciated.

Thank You

Ed

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

Re: DAX Topic FILTER() - Page 106 "Under the Hood"

Hi,

The way CALCULATE function works is - FILTER first, EVALUATE next. i.e. the Filter part of the formula is executed first and then the evaluation is done on the filtered table.

So in your under hood formula, you have the FILTER function that has ALL(Customers). This removes all initial filters applied and brings the entire Customers table.
So when you place the measure in the Matrix with Occupation on rows, the initial filter (Occupation) is removed and the formula is calculated over the entire Customers table.

That is the reason you get the same value on each row of the Matrix.

Hope it is clear now.

Best Wishes
Ramana
Ramana Varanasi
Training and Office Manager
Excelerator BI Pty. Ltd.

ed_at_wig
Posts: 3
Joined: Fri Nov 30, 2018 1:22 am

Re: DAX Topic FILTER() - Page 106 "Under the Hood"

Thank You Ramana, I appreciate your response.

I think what was confusing to me was this sentence just above the "under the hood" formula:
"Under the hood, the formula above is converted to the following formula:"

but the "following", "under the hood" formula:
Total Customers with Income of \$80,000 Under the Hood =
CALCULATE(COUNTROWS(Customers),FILTER(ALL(Customers),Customers[YearlyIncome]>=80000))

did not produce the same result as the "above 2" formula:
Total Customers with Income of \$80,000 or above 2 =
CALCULATE(COUNTROWS(Customers),Customers[YearlyIncome]>=80000)

I must have misunderstood the wording in the sentence.

Best Regards,

Ed
Attachments
2018-11-30_8-24-04.png (11.74 KiB) Viewed 1044 times

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

Re: DAX Topic FILTER() - Page 106 "Under the Hood"

Hi,

It is a typo in the second formula. It should be
Total Customers with Income of \$ 80,000 Under the Hood = CALCULATE(COUNTROWS(Customers), FILTER(Customers, Customers[YearlyIncome] >= 80000))

Thanks for bringing it out. We will add in errata.

With this formula the results are
FILTER Under hood.png (10.21 KiB) Viewed 1037 times

Thanks
Ramana
Ramana Varanasi
Training and Office Manager
Excelerator BI Pty. Ltd.

ed_at_wig
Posts: 3
Joined: Fri Nov 30, 2018 1:22 am

Re: DAX Topic FILTER() - Page 106 "Under the Hood"

Thanks alot Ramana. I really appreciate it.

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

Re: DAX Topic FILTER() - Page 106 "Under the Hood"

Hi,

I also made a mistake in it.

Following is the right formula.

Total Customers with Income of \$ 80,000 Under the Hood =
CALCULATE(COUNTROWS(Customers), FILTER(ALL(Customers[YearlyIncome]), Customers[YearlyIncome] >= 80000))
Ramana Varanasi
Training and Office Manager
Excelerator BI Pty. Ltd.