Forum Discussion
RANKX understanding
Hi folks,
I want to know why when I use (1.) code/procedure for Ranking it does not work and when I use (2.) code/procedure it's working well. What's the reason/logic behind it?
(1.)
Rank Requestors (not working2) =
(2.)
count of requests=COUNT(DB_01[num])
Rank Requestors (working)=RANKX(ALL(DB_01[Requestor full name]),[count of requests])
Rank Requestors (working1)=
)
Thanks in advance for your comments ! I simply don't get why I need to create a measure to count requests and then use it in RANKX function instead of using COUNT function directly in it.
hi CSSR
The other common pitfall is using a DAX formula to aggregate rows without wrapping the expression in a CALCULATE function. In the previous examples, we always used the measure Sales Amount as the expression to use in the ranking. If you use an aggregation function such as SUMX, you should consider that the expression is evaluated for each row of the table passed as a first argument to RANKX. The row context defined in this iteration is not transformed into a filter context unless a context transition is invoked by CALCULATE, which is an implicit operation when you evaluate a measure. Thus, for each row the filter context is always the same (that is, the existing filter in the cell where RANKX is evaluated), and all the items have the same rank of 1 in this way. The correct formula simply wraps the expression in a CALCULATE function that performs the context transition for each row of the table iterated by RANKX.
Regards,
Lin
2 Replies
CSSR , You have use calculate or a measure. This not rankx true with most of the X measure. You have use calculate or measure for correct results
For Rank Refer these links, first has more details
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415hi CSSR
The other common pitfall is using a DAX formula to aggregate rows without wrapping the expression in a CALCULATE function. In the previous examples, we always used the measure Sales Amount as the expression to use in the ranking. If you use an aggregation function such as SUMX, you should consider that the expression is evaluated for each row of the table passed as a first argument to RANKX. The row context defined in this iteration is not transformed into a filter context unless a context transition is invoked by CALCULATE, which is an implicit operation when you evaluate a measure. Thus, for each row the filter context is always the same (that is, the existing filter in the cell where RANKX is evaluated), and all the items have the same rank of 1 in this way. The correct formula simply wraps the expression in a CALCULATE function that performs the context transition for each row of the table iterated by RANKX.
Regards,
Lin
