Forum Discussion

shaunwilks's avatar
shaunwilks
Icon for Helper V rankHelper V
2 years ago
Solved

RANKX Calculated Column on a Related Tables

Sorry to have had to raise this request but I have looked far and wide for a resolution and tried perhaps 20 different DAX formulas for RANKX without the result I am looking for.  I have used samples on RADCAD posts that just apply a 1 to all values, and have used samples on other requests in the community that havent provided a result or errored.

The best way I can illustrate the problem/request is using a sports model.

I have two tables, a Players Table and a Games table (Sample Data below)

The join fields between the two tables is on a concatenation of Player, Year and Team.

 

I want to add...

1. A calculated column on the Players table that ranks an all time score rank across all players and all teams

2. A calculated column on the Players table that ranks where the player come in scoring for any particular Year

3. A calculated column on the Players table thats ranks where the player come in scoring for the year within their Team 

 

For 1.  I have a calculated column not working that that is a rank of where the player ranked for total scoring in a single Year.

 
All Time Rank = RANKX(ALL(Players), SUMX(RELATEDTABLE(Games), [Points]))
 
For 2. I have a calculated column that is close to working but the Ranks start at 2 and not 1. So I have to adjust the ranks and -1 from the calculation for it to be okay..
 
Rank within Year = RANKX ( ALLEXCEPT(  Players, Players[Year]), CALCULATE ( MINX(Players, Players[All Time Rank])) ,,ASC, Dense)-1
 
Players
Player TeamYear
John SmithPhilli2022
Julian KnightPhilli2022
John SmithNew York2023
Fred KraneLA Lakers2022
Andre SportHeat2022
Xavier FortLA Lakers2022
Fred KraneCeltics2023
Andre SportHeat2023
Xavier FortCeltics2023

 

Games

PlayerTeamYearVsPoints
John SmithPhilli2022New York14
John SmithPhilli2022LA Lakers15
John SmithPhilli2022Heat23
John SmithPhilli2022Celtics45
Julian KnightPhilli2022New York2
Julian KnightPhilli2022LA Lakers4
Julian KnightPhilli2022Heat7
Julian KnightPhilli2022Celtics8
John SmithNew York2022Philli21
John SmithNew York2022LA Lakers2
John SmithNew York2022Heat7
John SmithNew York2022Celtics9
Fred KraneLA Lakers2022New York12
Fred KraneLA Lakers2022Philli15
Fred KraneLA Lakers2022Heat11
Fred KraneLA Lakers2022Celtics10
Andre SportHeat2022New York17
Andre SportHeat2022Philli21
Xavier FortLA Lakers2022New York25
Xavier FortLA Lakers2022Philli13
Xavier FortLA Lakers2022Heat11
Xavier FortLA Lakers2022Celtics18
Xavier FortLA Lakers2022Bulls14
Fred KraneCeltics2023New York12
Fred KraneCeltics2023Philli16
Fred KraneCeltics2023Heat18
Andre SportHeat2023New York16
Andre SportHeat2023Philli22
Andre SportHeat2023Celtics13
Andre SportHeat2023Bulls4
Xavier FortCeltics2023Philli7
Xavier FortCeltics2023Heat9
Xavier FortCeltics2023New York32
Xavier FortCeltics2023Bulls12
Need Help
  • SamInogic's avatar
    SamInogic
    2 years ago

    Hi,

     

    We are a bit unclear about your requirement; if you can provide us the expected result, it will help us better to create DAX expressions.

    However, as per our understanding, you want ranking based on players' scores across different criteria. Here’s how you can achieve this using DAX in:

    Step 1: Create uniqueKey Keys

    First, create a uniqueKey key in both the Players and Games tables to help with the relationships.

    uniqueKey= [Player] & "-" & [Year] & "-" & [Team] 

    Step 2: Create Relationships

    1. Go to the Model View in Power BI.
    2. Create a relationship between the uniqueKey column in the Players table and the uniqueKey column in the Games table.

     

    Step 3: Create Measures and Calculated Columns

    Total Points Measure

    Create a measure to calculate the total points for each player:

    TotalPoints = SUM(Games[Points])

    Player Total Points

    Create a calculated column in the Players table to calculate the total points for each player:

    PlayerTotalPoints = CALCULATE(    [TotalPoints],    FILTER(        Games,        Games[uniqueKey] = Players[uniqueKey]    ))

    All-Time Rank

    Create a calculated column in the Players table for the all-time score rank:

    AllTimeRank = RANKX(    ALL(Players),    [PlayerTotalPoints],    ,    DESC,    DENSE)

    Rank Within Year

    Create a calculated column in the Players table for the rank within a specific year:

    RankWithinYear = RANKX(    FILTER(        ALL(Players),        Players[Year] = EARLIER(Players[Year])    ),    [PlayerTotalPoints],    ,    DESC,    DENSE)

    Rank Within Year and Team

    Create a calculated column in the Players table for the rank within a specific year and team:

    RankWithinYearAndTeam = RANKX(    FILTER(        ALL(Players),        Players[Year] = EARLIER(Players[Year]) &&        Players[Team] = EARLIER(Players[Team])    ),    [PlayerTotalPoints],    ,    DESC,    DENSE) 

     

    Please refer to the screenshot below for the results, 

     

    Hope this helps.

     

    Thanks!

2 Replies

  • SamInogic's avatar
    SamInogic
    Icon for Super User rankSuper User
    2 years ago

    Hi,

     

    We are a bit unclear about your requirement; if you can provide us the expected result, it will help us better to create DAX expressions.

    However, as per our understanding, you want ranking based on players' scores across different criteria. Here’s how you can achieve this using DAX in:

    Step 1: Create uniqueKey Keys

    First, create a uniqueKey key in both the Players and Games tables to help with the relationships.

    uniqueKey= [Player] & "-" & [Year] & "-" & [Team] 

    Step 2: Create Relationships

    1. Go to the Model View in Power BI.
    2. Create a relationship between the uniqueKey column in the Players table and the uniqueKey column in the Games table.

     

    Step 3: Create Measures and Calculated Columns

    Total Points Measure

    Create a measure to calculate the total points for each player:

    TotalPoints = SUM(Games[Points])

    Player Total Points

    Create a calculated column in the Players table to calculate the total points for each player:

    PlayerTotalPoints = CALCULATE(    [TotalPoints],    FILTER(        Games,        Games[uniqueKey] = Players[uniqueKey]    ))

    All-Time Rank

    Create a calculated column in the Players table for the all-time score rank:

    AllTimeRank = RANKX(    ALL(Players),    [PlayerTotalPoints],    ,    DESC,    DENSE)

    Rank Within Year

    Create a calculated column in the Players table for the rank within a specific year:

    RankWithinYear = RANKX(    FILTER(        ALL(Players),        Players[Year] = EARLIER(Players[Year])    ),    [PlayerTotalPoints],    ,    DESC,    DENSE)

    Rank Within Year and Team

    Create a calculated column in the Players table for the rank within a specific year and team:

    RankWithinYearAndTeam = RANKX(    FILTER(        ALL(Players),        Players[Year] = EARLIER(Players[Year]) &&        Players[Team] = EARLIER(Players[Team])    ),    [PlayerTotalPoints],    ,    DESC,    DENSE) 

     

    Please refer to the screenshot below for the results, 

     

    Hope this helps.

     

    Thanks!

    • shaunwilks's avatar
      shaunwilks
      Icon for Helper V rankHelper V
      2 years ago

      Whats an amazing response.

       

      Thankyou very much and have this working now.

      It would appear adding a column to the Players table in this instance that was the sumof points makes it easier to then base your rankings off the master table rather than keep going back to sum up the fact table.

       

      I will need to look more into you use of the "EARLIER" function in the filter as I hadnt seen that used so much in this sort of way. I dont understand it yet but will edcuate myself knowing that it did the job and works.