topic RANKX Only Returning 1 in DAX Commands and Tips

RANKX Only Returning 1

HelenBrunyee — Wed, 07 Apr 2021 13:20:33 GMT

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)

[Total Issued] = DISTINCTCOUNT(R_Issued[Reference]) - R_Issued is the list of tickets issued.

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

Re: RANKX Only Returning 1

amitchandak — Wed, 07 Apr 2021 13:26:57 GMT

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

Re: RANKX Only Returning 1

HelenBrunyee — Wed, 07 Apr 2021 13:52:46 GMT

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