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 sorted max to min will give their position in that season's league table. All I need is to be able to filter the data by season to get its rankx number. In the screenshot below you'll see the data. It's all in one table.  I'm not sure whether I should create a measure or a calculated column.

Expected result would be a chart which shows a team's position by year.

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 
)

     

     

13 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    ColinH It's all good, we all learn from each other. Glad you have multiple solutions. Cheers!!

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • 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)

         

         

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    ColinH I think this is what you want

     

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

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

     

    • ColinH's avatar
      ColinH
      Frequent Visitor
      4 years ago

      Thank you but no, that's not correct. Yours returns a value of 1 for 1993-94 when it should have been 4. I really appreciate you taking the time to reply though.

  • parry2k's avatar
    parry2k
    Super User
    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

      That's fantastic also! It works perfectly. Again, many thanks for being patient with a noob here.