topic Re: Rank DAX problem in Power BI in DAX Commands and Tips

Rank DAX problem in Power BI

datawiz23 — Tue, 05 Dec 2023 18:06:49 GMT

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)

)

Re: Rank DAX problem in Power BI

Fowmy — Tue, 05 Dec 2023 20:10:34 GMT

datawiz23

Please share a sample of your data. you can paste it here from excel ot csv

Re: Rank DAX problem in Power BI

datawiz23 — Wed, 06 Dec 2023 13:55:37 GMT

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

Re: Rank DAX problem in Power BI

Fowmy — Wed, 06 Dec 2023 19:21:50 GMT

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

Re: Rank DAX problem in Power BI

datawiz23 — Fri, 08 Dec 2023 10:33:08 GMT

Thanks Fowmy it worked a treat, the key was PARTITION I think. Kind regards, Mark