DAX - correct use of RANKX

Mike_R_

New Member
Joined
Jan 12, 2019
Messages
4
Dear Colleagues,
I wanted to practice with DAX but I got stuck incorrectly working RANKX function.
I worked on adventureworks data, Person_person table.
In 3rd column of upper table I counted occurances of persontype.
In next colummn I wanted to create a ranking using RankX function, therefore I created new measure as per attached printscreen (ranking = rankx('Person Person',count('Person Person'[PersonType]),,,Dense).
However a result of such ranking is 1 (last column in table)!
Can you please advise on how to fix the issue? I supose I need to modify DAX function...

Thanks in advance for help and reply!

Mike
 

Attachments

  • printscreen.png
    printscreen.png
    116.7 KB · Views: 5

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,240
When using RANKX, you are ranking an individual against all others. But the first parameter (the table) also receives the filter from the visual. So you have to first remove the filter. So wrap the first parameter inside ALL, like RANKX(ALL(person), etc
 

Mike_R_

New Member
Joined
Jan 12, 2019
Messages
4
Thanks very much for information Matt. However after applying ALL prior to table, it did not change anything (still I have 1 everywhere).
Maybe I need to apply ALL also while having count function in 2nd parameter....
Can you please advise?
 

Attachments

  • DAX rankx not working.png
    DAX rankx not working.png
    13.9 KB · Views: 3

Watch MrExcel Video

Forum statistics

Threads
1,114,524
Messages
5,548,553
Members
410,848
Latest member
anuradhagrewal
Top