Getting Top 5 base on DAX measure
I have two measures Rank PCM Simple = IF ( HASONEVALUE ( SUPPORT__CW_OPS_CES_CSAT_PARTNER_CARE_TEMP[SURVEY_OWNER_FULL_NAME] ), RANKX ( ALL ( SUPPORT__CW_OPS_CES_CSAT_PARTNER_CARE_TEMP[SURVEY_OWNER_FULL_NAME] ), [Avg_CSAT_Score] ) ) Top 3 PCM = IF( HASONEVALUE(SUPPORT__CW_OPS_CES_CSAT_PARTNER_CARE_TEMP[SURVEY_OWNER_FULL_NAME]), IF( [Rank PCM Simple] <= 3, [Avg_CSAT_Score], BLANK() ) ) Based on this I am getting the top 3 but i now need top 5 SURVEY_OWNER_FULL_NAME, however, no matter what I change in [Rank PCM Simple] <= 3 under the 2nd measure I am unable to get top 5. Idrissshatila amitchandak parry2k
DAX for ranking based on 3 columns
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
