Performing ranking in DAX is simple enough when you are considering just single numerical values.
I mean, for example, it’s easy to rank Products based on Total Sales.
But consider the scenario below. A classical problem in sporting league standings. Where there can be multiple ties on various values:
Assuming the rules for ranking teams are based on the following:
Looking at the table above, all the above rules are in play. Here are some examples
Wahala be like bicycle (Problem is like bicycle – Nigerian saying, meaning, like bicycle, problem is not a difficult thing to start).
The first thing to solve any problem, including DAX is to break it down into smaller pieces.
Isolate the teams based on ties from the above ranking and carry out another ranking based on Goals Difference.
Here for example, if there 3 teams tied on 1st position, the isolated ranking will have them ranked from 1 to 3.And if there is only 1 team on 4th position for example, the isolated ranking will have that team ranked as 1 since there is no competition there.
1. InitialRanking =
RANKX (
ALL ( ‘League Standing'[Club] ),
CALCULATE ( SUM ( ‘League Standing'[Points] ) ), ,
DESC,
SKIP
)
2. Second Ranking =
VAR InitialRanking =
[InitialRanking]
VAR Ties =
FILTER (
ALL ( ‘League Standing'[Club] ),
[InitialRanking] = InitialRanking
)
RETURN
RANKX (
Ties,
CALCULATE (
SUM ( ‘League Standing'[Goals Difference] )
), ,
DESC,
SKIP
)
3. Third Ranking =
[InitialRanking] + [Second Ranking] – 1
4. Fourth Ranking =
VAR ThirdRanking =
[Third Ranking]
VAR Ties =
FILTER (
ALL ( ‘League Standing'[Club] ),
[Third Ranking] = ThirdRanking
)
RETURN
RANKX (
Ties,
CALCULATE (
SUM ( ‘League Standing'[Goals Against] )
), ,
ASC,
SKIP
)
5. Fifth Ranking =
[Third Ranking] + [Fourth Ranking] – 1
6. Sixth Ranking =
VAR FifthRnking = [Fifth Ranking]
VAR Ties =
FILTER (
ALL ( ‘League Standing'[Club] ),
[Fifth Ranking] = FifthRnking
)
RETURN
RANKX (
Ties,
CALCULATE (
MIN ( ‘League Standing'[Club] )
), ,
ASC,
SKIP
)
7. Seventh Ranking =
[Fifth Ranking] + [Sixth Ranking] – 1]
In the end, only the final ranking will be visualized.
Yeah, I know how complicated DAX can seem. But in the end, not giving up on mastering it is the right way to go.
You can check my YouTube Course on Mastering DAX Foundation to gain a solid foundation of the DAX Language. Please subscribe to the channel too.