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 
)

     

     

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

      • ColinH's avatar
        ColinH
        Frequent Visitor
        4 years ago

        Gosh, that's it. I would never have got that in a month of Sundays. I've never even heard of EARLIER. Thank you so much for taking the time to help, it's very kind of you.