Alright, here is a new way to hurt your brain.
Ask:
You have to explain why the first calculation works but second doesnât.
Data:
DAX Challenge 4 - RANKX.pbix (168.2 KB)
Report
Scenario:
We have 2 measures that calculate RANK based on Total Sales, one of them works and one of them doesnât
Measure that works:
Measure that doesnât work:
Have fun!
My thinking is that by using CALCULATE and REMOVEFILTERS youâre removing the filter context of Brand in the table. Calculate is a bully and overrides the filter context of BrandâŚbut then is the use of VALUES causing you not to get a returnâŚmmmmmmmmnnn
So as VALUES returns a distinct list do you only get a list of 1 for each row so you only end up with a rank of 1 for each row?
Hello @AntrikshSharma
I think REMOVEFILTERS is modifying the filter context here.
Here is a clue, in this calculation REMOVEFILTERS does remove the filter from Brand but Context Transition doesnât happen before REMOVEFILTERS so REMOVEFITLERS doesnât remove the filter created by Context Transition.
VALUES will return all the Brands
Hi AntrikshSharma,
One query, why in the following measure AllTotalSalesBrand, REMOVEFILTERS if it removes the filter created by Context Transiton?
When you say that VALUES will return all the Brands, by not eliminating the filter created by Context Transiton, the opposite happens, right?
Regards,
@jafernandezpuga REMOVEFILTERS is a CALCULATE Modifier so it is evaluated before the first argument of CALCULATE, So VALUES returns all the Brands but the Context Transition happens after REMOVEFILTERS removes the filter from Brand.
Hi Antriksh
Here is why the measures are different.
The measure Brand Rank 2 for each row of the report is behaving in a similar way to the Brand Rank measure at the total level.
When using REMOVEFILTERS for each row of the report, VALUES returns all brands.
RANKX first creates a temporary lookup table with all the brands, where for each row of this temporary table (i.e. brand) the expression [Total Sales] is evaluated in a row context. The use of a measure [Total Sales] converts the row context into an equivalent filter context (context transition). Once this operation is completed, the temporary table will look similar to the 1st two columns of the report but without the total (i.e. one column that has the brand, with the corresponding sales amount for each brand in the second column).
Once the temporary lookup table is built, the expression [Total Sales] is then evaluated in the filter context of the report to determine the position (i.e. rank) against the lookup table values in the second column.
The first part, i.e. creation of the temporary lookup table is identical for the 2 measures.
However, the second part isnât. In the Brand Rank 2 measure, the filter context has the full list of brands (with the use of REMOVEFILTERS) and therefore, when the expression [Total Sales] is evaluated this returns the total sales value of 2.229m. When this is compared to the lookup table to determine the rank, it will always be 1 since the sum of all brands will be greater than the value for each brand.
Thanks
@M_K 100% correct! You Win!
