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

[ed_at_wig]

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]

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]

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

[RamanaV]

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 1036 times

Thanks
Ramana

[ed_at_wig]

Thanks alot Ramana. I really appreciate it.

[RamanaV]

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