Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I was hoping you would be able to help me. I have tried various options from other forum posts, but have not got anywhere.
I have created a RANKX formula to rank the sites I have, by the number of tickets they have issued, but it always ranks all of the sites as 1 in my table.
I originally used this DAX fomula, with no sucess:
Site Rank = RANKX (
ALLSELECTED(V_Locations[Location Name]), CALCULATE([Total Issued])),, DESC, DENSE)
I then tried the following, based on another post, which worked for them, but I still get them all as 1:
Site Rank = RANKX (
ALLSELECTED(V_Locations[Location Name]), CALCULATE(DISTINCTCOUNT(R_Issued[Reference])),, DESC, DENSE)
Could anyone tell me what I am doing wrong?
Any help would be much appreciated!
Best wishes,
Helen
Solved! Go to Solution.
@HelenBrunyee , If you use any other column in visual than V_Locations[Location Name]) then rank will repeat inside that column .
Assume you use location id , then you will get all rank as 1, as there is only one location inside location id
then you have to try
Site Rank = RANKX (
ALLSELECTED(V_Locations[Location Name],V_Locations[Location ID]), CALCULATE(DISTINCTCOUNT(R_Issued[Reference])),, DESC, DENSE)
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://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
@HelenBrunyee , If you use any other column in visual than V_Locations[Location Name]) then rank will repeat inside that column .
Assume you use location id , then you will get all rank as 1, as there is only one location inside location id
then you have to try
Site Rank = RANKX (
ALLSELECTED(V_Locations[Location Name],V_Locations[Location ID]), CALCULATE(DISTINCTCOUNT(R_Issued[Reference])),, DESC, DENSE)
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://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
You're a star! Thank you so much @amitchandak 🙂
That worked perfectly, and also your explanation makes much more sense as to why it wasn't working and how to use RNAKX in the future.
Thanks again,
Helen