Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi All,
I feel like this one should be really simple, but I can't seem to work it out. I have a very basic, static table with a persons name and a score. I would like to rank those score within the individual. Example:
Dataset:
Person | Score |
John | 50 |
John | 75 |
John | 50 |
John | 100 |
Tom | 50 |
Tom | 75 |
Tom | 100 |
Tom | 100 |
Dave | 50 |
Dave | 75 |
Sally | 50 |
Desired Result:
Person | Score | Rank |
Dave | 50 | 2 |
Dave | 75 | 1 |
John | 50 | 3 |
John | 50 | 3 |
John | 75 | 2 |
John | 100 | 1 |
Sally | 50 | 1 |
Tom | 50 | 3 |
Tom | 75 | 2 |
Tom | 100 | 1 |
Tom | 100 | 1 |
If I use Rank = Rankx('Table','Table'[Score],,DESC,DENSE) it will rank all of the scores, not bound to an individual.
If I use Rank = Rankx(ALLEXCEPT('Table','Table'[Person]),'Table'[Score],,DESC,DENSE) then I get a circular dependancy error.
Am I missing something obvious here? I feel like I am...
Thanks again for all your help!
Solved! Go to Solution.
The ALLEXCEPT function is a bit weird when used as a table function. I you're trying to write a calculated column, I'd suggest this version instead.
RANKX (
FILTER ( 'Table', 'Table'[Person] = EARLIER ( 'Table'[Person] ) ),
'Table'[Score],, DESC, DENSE
)
The ALLEXCEPT function is a bit weird when used as a table function. I you're trying to write a calculated column, I'd suggest this version instead.
RANKX (
FILTER ( 'Table', 'Table'[Person] = EARLIER ( 'Table'[Person] ) ),
'Table'[Score],, DESC, DENSE
)
Hi AlexisOlson,
Works like a charm! Thank you for the assist.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |