Forum Discussion

ColinH's avatar
ColinH
Frequent Visitor
4 years ago
Solved

RankX problems

Hi, I struggle with RANKX when filtering. Can someone help me with this problem, please? I have a table that lists soccer seasons with teams. I've created a new column (AllSeasonsFiling) that, when ...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    ColinH  you can create a column like following

    Column = RANKX(FILTER('Table 1','Table 1'[Season]=EARLIER('Table 1'[Season])),'Table 1'[AllSeasonsFiling],,DESC)

     

    yet to figure out the measure

  • parry2k's avatar
    parry2k
    4 years ago

    ColinH here is your measure, ofcourse you already have the solution but we always want to avoid adding a column (where possible):

     

    Rank Team Season = 
RANKX ( 
    FILTER ( 
        ALL ( TeamSeason ),
        TeamSeason[Season] = MAX ( TeamSeason[Season] ) 
    ), 
    CALCULATE ( MAX ( TeamSeason[AllSeasonsFiling] ) ), , 
    DESC 
)

     

     

smpa01's avatar
smpa01
Community Champion
4 years ago

ColinH  you can create two measures like this

_allSeasonFiling = MAX('Table 1'[AllSeasonsFiling])

_rank = RANKX(ALLSELECTED('Table 1'[Team]),[_allSeasonFiling],,DESC)

 

 

ColinH's avatar
ColinH
Frequent Visitor
4 years ago

Hi, thank you so much, but it's not quite doing what I'd hoped - probably my fault for explaining badly. I wish the filter to be by team, and the result to be a table showing that team's position over multiple years. Your solution has it the other way round. I'm afraid I don't seem to be able to upload a pbix like you, so I can only show a screenshot. Here Arsenal are selected and they are incorrectly evaluating to rank 1. (This is using your provided pbix, not mine, so the data is the same as I attached earlier)

 

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    ColinH  so with this given selection, what values do you expect dax to return? 

    • ColinH's avatar
      ColinH
      Frequent Visitor
      4 years ago

      I'd expect Arsenal 1992-93 to show 10 (the 10th highest _allseasonfiling), 1993-94 to show 4 and, given I provided incomplete data for 1994-95, I'd expect that to show 12.

      • smpa01's avatar
        smpa01
        Community Champion
        4 years ago

        ColinH  you can create a column like following

        Column = RANKX(FILTER('Table 1','Table 1'[Season]=EARLIER('Table 1'[Season])),'Table 1'[AllSeasonsFiling],,DESC)

         

        yet to figure out the measure