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  please provide sample data in table format here.

ColinH's avatar
ColinH
Frequent Visitor
4 years ago

Apologies, smpa01 hopefully I've done this correctly

 

SeasonTeamAllSeasonsFiling
1992-93Arsenal5602040
1992-93Aston Villa7417057
1992-93Blackburn7122068
1992-93Chelsea5597051
1992-93Coventry5195052
1992-93Crystal Palace4887048
1992-93Everton5298053
1992-93Ipswich5195050
1992-93Leeds5095057
1992-93Liverpool5907062
1992-93Man City5705056
1992-93Man Utd8436067
1992-93Middlesbrough4379054
1992-93Norwich7196061
1992-93Nottingham F3979041
1992-93Oldham4889063
1992-93QPR6308063
1992-93Sheff Utd5201054
1992-93Sheff Weds5904055
1992-93Southampton4993054
1992-93Tottenham5894060
1992-93Wimbledon5401056
1993-94Arsenal7125053
1993-94Aston Villa5696046
1993-94Blackburn8427063
1993-94Chelsea5096049
1993-94Coventry5598043
1993-94Everton4379042
1993-94Ipswich4277035
1993-94Leeds6825065
1993-94Liverpool6004059
1993-94Man City4489038
1993-94Man Utd9242080
1993-94Newcastle7741082
1993-94Norwich5304065
1993-94Oldham3974042
1993-94QPR6001062
1993-94Sheff Utd4182042
1993-94Sheff Weds6422076
1993-94Southampton4283049
1993-94Swindon2947047
1993-94Tottenham4293052
1993-94West Ham5392048
1993-94Wimbledon6503056
1994-95Arsenal5103052
1994-95Aston Villa4795051
1994-95Blackburn8941080
1994-95Chelsea5395050
1994-95Coventry4982044
1994-95Crystal Palace4485034
1994-95Everton4993044
1994-95Ipswich2643036
1994-95Leeds7321059
1994-95Leicester2865045
1994-95Liverpool7428065
1994-95Man City4889053
1994-95Man Utd8849077
1994-95Newcastle7220067
1994-95Norwich4283037
1994-95Nottingham F7729072
1994-95QPR6002061
1994-95Sheff Weds5092049
1994-95Southampton5398061
1994-95Tottenham6208066
1994-95West Ham4996044
1994-95Wimbledon5583048
  • 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?