Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
This problem has been sending me potty.
I know it should be relatively be simple but I can't crack it, originally I tried to solve it using RANKX but I couldn't get it to work so I tried RANK and still no joy, could someone help?
The semantic model I'm using is for the Times University Rankings, all years
I have a ranking value for each university and in it's simplest form I just want to rank a university's position within it's country.
A really simple example is in 2011, there were two universities in Austria in the Times university Top 200:
University of Vienna - ranked 195 overall &
University of Innsbruck - ranked 187 overall
All I want to do is to introduce a DAX measure that ranks the order at Year, Country, University level:
so Innsburck would be 1 and Vienna 2
Please could someone help and save me from insanity?
I thought it might look something like this but with extra fields, no matter what I try I just can't get it to work, thanks Mark:
Rank University by Country =
RANK (
DENSE,
ALLSELECTED ( 'fact' [university]),
ORDERBY ( [ times ranking], DESC)
)
Solved! Go to Solution.
@datawiz23
Create the following measure to rank by Rating under each Country:
Uni Rank =
VAR __T = ALLSELECTED( 'Table' )
VAR __Result =
RANK(
DENSE,
__T ,
ORDERBY('Table'[World Rank in Year],ASC),,
PARTITIONBY( 'Table'[country])
)
RETURN
__Result
File attached below
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
sorry for the delay @Fowmy
here is some data from the model
year | country | University | World Rank in Year |
2023 | Albania | Polytechnic University of Tirana | 100036.8 |
2019 | Algeria | Badji Mokhtar University – Annaba | 1023 |
2022 | Algeria | Badji Mokhtar University – Annaba | 1234 |
2023 | Algeria | Badji Mokhtar University – Annaba | 1528 |
2021 | Algeria | Blida 1 University | 1066 |
2022 | Algeria | Blida 1 University | 1251 |
2023 | Algeria | Blida 1 University | 1541 |
2023 | Algeria | École Nationale Polytechnique | 100013.7 |
2023 | Algeria | École Polytechnique d’Architecture et d’Urbanisme | 100013.8 |
2019 | Algeria | Ferhat Abbas Sétif University 1 | 871 |
2020 | Algeria | Ferhat Abbas Sétif University 1 | 652 |
2021 | Algeria | Ferhat Abbas Sétif University 1 | 527 |
2022 | Algeria | Ferhat Abbas Sétif University 1 | 536 |
2023 | Algeria | Ferhat Abbas Sétif University 1 | 426 |
kind regards,
Mark
@datawiz23
Create the following measure to rank by Rating under each Country:
Uni Rank =
VAR __T = ALLSELECTED( 'Table' )
VAR __Result =
RANK(
DENSE,
__T ,
ORDERBY('Table'[World Rank in Year],ASC),,
PARTITIONBY( 'Table'[country])
)
RETURN
__Result
File attached below
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks @Fowmy it worked a treat, the key was PARTITION I think. Kind regards, Mark
@datawiz23
Please share a sample of your data. you can paste it here from excel ot csv
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
59 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
87 | |
59 | |
52 | |
37 | |
21 |