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
DAX Topic FILTER()  Page 106 "Under the Hood"
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
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.
Training and Office Manager
Excelerator BI Pty. Ltd.
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.
Thanks again for your response.
Best Regards,
Ed
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

 20181130_82404.png (11.74 KiB) Viewed 1043 times
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
Thanks
Ramana
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
Thanks
Ramana
Ramana Varanasi
Training and Office Manager
Excelerator BI Pty. Ltd.
Training and Office Manager
Excelerator BI Pty. Ltd.
Re: DAX Topic FILTER()  Page 106 "Under the Hood"
Thanks alot Ramana. I really appreciate it.
Re: DAX Topic FILTER()  Page 106 "Under the Hood"
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))
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))
Ramana Varanasi
Training and Office Manager
Excelerator BI Pty. Ltd.
Training and Office Manager
Excelerator BI Pty. Ltd.
Return to “Help with the Book "Supercharge Power BI"”
Who is online
Users browsing this forum: No registered users and 1 guest