topic Re: DAX for ranking based on 3 columns in DAX Commands and Tips

DAX for ranking based on 3 columns

albrecht — Sun, 16 Jul 2023 21:56:43 GMT

Hello,

I am trying to solve a simple task but I am not able to find a solution by my own so I kindly ask you for help.

Context: I am creating a statistics for beachvolleyball. There are about 20 tounaments every season. Every tournament 4 players compete, based on the their score (sets won minus sets lost) players got tournament standing - for example 1st for the best player, 2.5 for two players sharing the 2nd and 3rd place and 4th for the loser. The list of 4 players attending the tournaments differ tournament by tournament. There is 5 "main" players for which the statistics are created, the other players are just "guests". I am preparing the seasonal stats aggregation the results of all the tournaments. The seasonal standing is calculated based on the 3 dimensions:

1. average tournament standing (ASC)

2. (when no. 1 equals among two or more players, then) number of the best tournament standings minus number of the worst tournament standings (DESC)

3. (when no. 2 equals among two or more players, then) number of sets won minus number of sets lost (DESC)

Data:

"Tournaments" table containg for every player for every tournament at least following (M language transformations):

- [Player name] column as String

- [Player type] as String: "main player" or "guest"

- [Player standing] as Decimal

- [Player result] as Integer: 1 = tournament winner, 2 = middle, 3 = tournament loser

- [Sets won - sets lost] as Integer

Then I created following DAX meaures in the same table "Tournaments"

- [1. Average standing] = average of [Player standing] column for every player

- [2. Player results agg] = count of "1" in [Player result] column minus count of "3" in [Player result] for every player

- [3. Sets won - sets lost agg] = sum of [Sets won - sets lost] for every player

Now I need the calculate the seasonal ranking of the players based on the 3 measures above. It can be done as a single calculation or even separate rankings of all the 3 measures are fine for me as I am able to use a "proxy score" (10.000*Ranking based on the 1st measure - 1.000* Ranking based on the 2nd measure - Ranking based on the 3rd measure) and the get the final ranking of the players based on the proxy score ASC. In both cases, I need to calculate seasonal ranking for the group of main players separately, for the group of the guests separately and for both group together without considering main player vs. guest.

Thank you very much for your help.

Jakub Albrecht

Re: DAX for ranking based on 3 columns

rubayatyasmin — Mon, 17 Jul 2023 11:11:41 GMT

Hi, albrecht

First, we need to create a table that will have a single row for each player, along with the necessary metrics. This can be achieved by using the SUMMARIZE function in DAX.

then you need to use Rankx to get the seasonal ranking. In this step you should also get the proxy scores.

now you have the proxy scores and seasonal ranking. So use RANKX again to get the final ranking.

To get the rankings separately for main players and guests, you can filter the SeasonalRanking table before calculating the final rank.

for example.

PlayerMetrics =
SUMMARIZE(
Tournaments,
Tournaments[Player name],
Tournaments[Player type],
"Average Standing", [1. Average standing],
"Player Results Agg", [2. Player results agg],
"Sets Won - Sets Lost Agg", [3. Sets won - sets lost agg]
)

2. SeasonalRanking =
ADDCOLUMNS(
PlayerMetrics,
"1st Measure Rank", RANKX(PlayerMetrics, [Average Standing], , ASC),
"2nd Measure Rank", RANKX(PlayerMetrics, [Player Results Agg], , DESC),
"3rd Measure Rank", RANKX(PlayerMetrics, [Sets Won - Sets Lost Agg], , DESC),
"Proxy Score", 10000 * [1st Measure Rank] - 1000 * [2nd Measure Rank] - [3rd Measure Rank]
)

3. FinalRanking =
ADDCOLUMNS(
SeasonalRanking,
"Final Rank", RANKX(SeasonalRanking, [Proxy Score], , ASC)
)

FinalRankingMain =
ADDCOLUMNS(
FILTER(SeasonalRanking, SeasonalRanking[Player type] = "main player"),
"Final Rank Main", RANKX(FILTER(SeasonalRanking, SeasonalRanking[Player type] = "main player"), [Proxy Score], , ASC)
)

FinalRankingGuest =
ADDCOLUMNS(
FILTER(SeasonalRanking, SeasonalRanking[Player type] = "guest"),
"Final Rank Guest", RANKX(FILTER(SeasonalRanking, SeasonalRanking[Player type] = "guest"), [Proxy Score], , ASC)
)

something like this. I think it is enough to get an idea.

If my assistance helped you in any way, hit 👍.

Re: DAX for ranking based on 3 columns

albrecht — Thu, 20 Jul 2023 09:44:19 GMT

Hi, rubayatyasmin,

thanks for the direction. I realized I skipped the summarization step and that was why my ranking attemps did not work.

But i your approach I experience a new trouble. I use 2 slicers to filter the content and the result od the table summarization is statis - does not reflect the filters from the slicers.

The two slicers offer following filtering options:

Tournament type
table 'List of tournaments'[Tournament type]: value "only main player" when all players are main players, value "not only main players" when at least one player is not a main player
Combined 4-level slicer:
- table 'List of seasons'[Attendees]: men vs. mixed (men+women)
  - 'List of seasons'[Season type]: summer vs. winter
    - 'List of seasons'[Season]: Season unique name, e.g. Summer season 2023
      - 'List of tournaments'[Date]: date of the specific tournament, e.g. 17/07/2023

Please do you have any idea how to keep considering the filters from two slicers above to the summarized table used for ranking calculation?

I can send you .pbix file if you prefer, but all the texts inside are in Czech langugage (different to what I state in this post, where I transafer the names of the tables and columns.

Thank you very much for your help!

Re: DAX for ranking based on 3 columns

rubayatyasmin — Thu, 20 Jul 2023 11:17:51 GMT

try this one.

PlayerMetrics =
CALCULATETABLE (
SUMMARIZE(
Tournaments,
Tournaments[Player name],
Tournaments[Player type],
"Average Standing", [1. Average standing],
"Player Results Agg", [2. Player results agg],
"Sets Won - Sets Lost Agg", [3. Sets won - sets lost agg]
),
ALLSELECTED('List of tournaments'[Tournament type]),
ALLSELECTED('List of seasons'[Attendees]),
ALLSELECTED('List of seasons'[Season type]),
ALLSELECTED('List of seasons'[Season]),
ALLSELECTED('List of tournaments'[Date])
)

SeasonalRanking =
ADDCOLUMNS (
PlayerMetrics,
"1st Measure Rank", RANKX(ALL(PlayerMetrics), [Average Standing], , ASC),
"2nd Measure Rank", RANKX(ALL(PlayerMetrics), [Player Results Agg], , DESC),
"3rd Measure Rank", RANKX(ALL(PlayerMetrics), [Sets Won - Sets Lost Agg], , DESC),
"Proxy Score", 10000 * [1st Measure Rank] - 1000 * [2nd Measure Rank] - [3rd Measure Rank]
)

FinalRanking =
ADDCOLUMNS (
SeasonalRanking,
"Final Rank", RANKX(ALL(SeasonalRanking), [Proxy Score], , ASC)
)

FinalRankingMain =
ADDCOLUMNS (
FILTER(SeasonalRanking, SeasonalRanking[Player type] = "main player"),
"Final Rank Main", RANKX(FILTER(ALL(SeasonalRanking), SeasonalRanking[Player type] = "main player"), [Proxy Score], , ASC)

)

FinalRankingGuest =
ADDCOLUMNS (
FILTER(SeasonalRanking, SeasonalRanking[Player type] = "guest"),
"Final Rank Guest", RANKX(FILTER(ALL(SeasonalRanking), SeasonalRanking[Player type] = "guest"), [Proxy Score], , ASC)
)

you might need to adjust. And you need to check a way to use Summarize with allselected. this should work