topic Re: RANKX not responding to filters or anything in DAX Commands and Tips

RANKX not responding to filters or anything

Anonymous — Wed, 08 Feb 2023 14:51:20 GMT

Hi,

I'm trying to create a leaderboard based on Total Points made by each country.

I used this formula

_Rank = RANKX( ALL('GSW Active'[Country]), [_Total Points], , DESC)

The No. Column is just a bunch of 1's... Any ideas how I can make it work? I've tried for hours...

It did work before, not sure how I messed it up... but I did have one more issue.

I wanted to rank based on a 2nd measure (tried multiple formulas, nothing worked), want to use "Hired/HC" as most of the time the countries have the same number of points so the No. column is repeating ranking because of that: 1, 2, 3, 3, 3, 6, 7, 7, 9 etc.

I tried _Rank = RANKX( ALL('GSW Active'[Country]), [_Total Points] * [_Hired/HC], , DESC) but it doesn't apply in this case.

All columns are from the same table, structured like this:

Country / Month / Total Points / Hired / HC / "Hired/HC"

There is a slicer on the page for the Month (date), not sure if it needs to be added in the measure.

Re: RANKX not responding to filters or anything

Greg_Deckler — Wed, 08 Feb 2023 14:06:06 GMT

Anonymous Is your formula actually:

_Rank = RANKX( ALL('GSW Active'[Country]), [_Total Points], , DESC)

Note the additional paren ) after [Country]

You may find this theraputic: To *Bleep* with RANKX! - Microsoft Power BI Community

Re: RANKX not responding to filters or anything

Anonymous — Wed, 08 Feb 2023 16:38:42 GMT

Thanks Greg_Deckler

So I found out that the flags were at fault... changed the measure to include flags as well:

_Rank = RANKX(ALL('GSW Active'[Country], 'GSW Active'[Flags URL]), [_Total Points], ,DESC, DENSE)

Can I can do something about those countries that have equal points? I'd like to add the highest _Hired/HC as 2nd priority somehow.

Re: RANKX not responding to filters or anything

Greg_Deckler — Wed, 08 Feb 2023 17:20:17 GMT

Anonymous Well, you can do something about ties using this approach: The Mythical DAX Index - Microsoft Power BI Community

Re: RANKX not responding to filters or anything

Anonymous — Wed, 08 Feb 2023 19:19:00 GMT

My final form was:

_Rank = RANKX(ALL('GSW Active'[Country], 'GSW Active'[Flags URL]), [_Total Points] + [_Hired/HC], ,DESC, DENSE)

Not fully happy with it... but... my head is exploding...

Tomorrow is another day...