Forum Discussion
RANKX DENSE functionality
Dear all,
Every now and then I stumble along the rankx function. This is what I use. Fairly simple.
PRODUCTSCAN = COUNTROWS('MyDataTablev 1)
| Productscan | EAN | language | Rank |
| 23 | 123 | de | 1 |
| 18 | 1234 | de | 2 |
| 10 | 12345 | de | 3 |
| 10 | 123456 | de | 3 |
HI Anonymous ,
You need to break the RANK TIES.
https://docs.microsoft.com/en-us/dax/rankx-function-dax
The functionality of dense is
Dense The next rank value, after a tie, is the next rank value. For example if five (5) values are tied with a rank of 11 then the next value will receive a rank of 12.
You will need a rank breaker.
Use RAND
RANKING = RANKX(ALLSELECTED('MyDataTablev 1'[Product EAN],'MyDataTablev 1'[language]),[PRODUCTSCAN] + RAND(),,DESC,dense)
else
RANKING = RANKX(FILTER(ALLSELECTED('MyDataTablev 1'[language] = MAX('MyDataTablev 1'[language])),[PRODUCTSCAN] ,,DESC,dense)
Else please share some sample data and the expected output
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
5 Replies
HI Anonymous ,
You need to break the RANK TIES.
https://docs.microsoft.com/en-us/dax/rankx-function-dax
The functionality of dense is
Dense The next rank value, after a tie, is the next rank value. For example if five (5) values are tied with a rank of 11 then the next value will receive a rank of 12.
You will need a rank breaker.
Use RAND
RANKING = RANKX(ALLSELECTED('MyDataTablev 1'[Product EAN],'MyDataTablev 1'[language]),[PRODUCTSCAN] + RAND(),,DESC,dense)
else
RANKING = RANKX(FILTER(ALLSELECTED('MyDataTablev 1'[language] = MAX('MyDataTablev 1'[language])),[PRODUCTSCAN] ,,DESC,dense)
Else please share some sample data and the expected output
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)Hi Anonymous amitchandak ,
Thanks for the tips!! my understanding was that "dense" does the job in my example above. So the last entry im my example table should have the ranking number four?
Anonymous - No, dense will still not break ties. To break ties, use the RAND method that Anonymous mentioned. Also, there is the DAX Index way of doing it too. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Mythical-DAX-Index/m-p/1093214#M528
Anonymous , if the value us the same and you want to break ties use
https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
For Rank Refer these links
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://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415Hi Anonymous ,
The Dense argument will return the same rank number 3 instead of 4 if the ranked values are the same. For the next rank value, after a tie, is the next rank value. For example if five (5) values are tied with a rank of 11, then the next value will receive a rank of 12. See more about RANKX function here https://docs.microsoft.com/en-us/dax/rankx-function-dax.
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
