The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello all,
Hope this message finds you well and please take into account I'm new to Power BI.
I'm looking at some "fake" FIFA 23 data (provided via Kaggle) and I'm trying to create a table that shows me who are the best players to play at specific position. Every player has a best position to play (with the best overall skill), however they can play anywhere in the field and they have a skill level for every position they can play. For example, if player A is better on a Striker Position, his skill value on that position is 91, however he can also play has a midfielder but his skill will be 78; and so on.
With this information I want to create multiple tables(one for each position) that ranks every player skill value for a particular position, however I'm having a real hard time doing that. I'm trying to use the Rankx and Summarize DAX fucntions, but I tried multiple things. It never works.
Here's a sample of the table that comes from power query:
Player | Best Position | Skill | Skill Value | Positon | Position Value |
Mbappe | ST | Pace Total | 97 | CAM | 92 |
Mbappe | ST | Passing Total | 90 | CAM | 92 |
Mbappe | ST | Dribbling Total | 92 | CAM | 92 |
Mbappe | ST | Pace Total | 97 | CB | 57 |
Mbappe | ST | Passing Total | 90 | CB | 57 |
Mbappe | ST | Dribbling Total | 92 | CB | 57 |
Messi | CAM | Pace Total | 81 | CAM | 91 |
Messi | CAM | Passing Total | 90 | CAM | 91 |
Messi | CAM | Dribbling Total | 94 | CAM | 91 |
Messi | CAM | Pace Total | 81 | CB | 53 |
Messi | CAM | Passing Total | 90 | CB | 53 |
Messi | CAM | Dribbling Total | 94 | CB | 53 |
Here's what I'm looking for:
Player | Position | Positon Value | Ranking |
Mbappe | CAM | 92 | 1 |
Messi | CAM | 91 | 2 |
Mbappe | CB | 57 | 1 |
Messi | CB | 53 | 2 |
After getting a table with this, I can filter with the position on the visual side of the final table.
Here's my current formula for rankx
Solved! Go to Solution.
Hi @Kuri_190 ,
Please try below steps:
1. create a measure with below dax formula
Ranking =
VAR cur_position =
SELECTEDVALUE ( 'Table'[Positon] )
VAR tmp =
FILTER ( ALL ( 'Table' ), 'Table'[Positon] = cur_position )
RETURN
RANKX ( tmp, CALCULATE ( MAX ( 'Table'[Position Value] ) ),,, DENSE )
2. add a table visual with measure and fields
Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Kuri_190 ,
Please try below steps:
1. create a measure with below dax formula
Ranking =
VAR cur_position =
SELECTEDVALUE ( 'Table'[Positon] )
VAR tmp =
FILTER ( ALL ( 'Table' ), 'Table'[Positon] = cur_position )
RETURN
RANKX ( tmp, CALCULATE ( MAX ( 'Table'[Position Value] ) ),,, DENSE )
2. add a table visual with measure and fields
Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
15 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
23 | |
14 | |
13 | |
8 | |
8 |