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"

Postby ed_at_wig » Fri Nov 30, 2018 1:36 am

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: 29
Joined: Thu Oct 19, 2017 12:57 pm

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

Postby RamanaV » Fri Nov 30, 2018 8:38 am

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

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

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

Postby ed_at_wig » Fri Nov 30, 2018 11:30 pm

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.

Thanks again for your response.

Best Regards,

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

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

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

Postby RamanaV » Sat Dec 01, 2018 9:32 am

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
FILTER Under hood.png (10.21 KiB) Viewed 174 times


Thanks
Ramana

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

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

Postby ed_at_wig » Mon Dec 03, 2018 6:27 am

Thanks alot Ramana. I really appreciate it.

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

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

Postby RamanaV » Mon Dec 03, 2018 8:11 am

Hi,

I asked Matt Allington about this formula.

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))


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

Who is online

Users browsing this forum: No registered users and 1 guest