Forum Discussion

datawiz23's avatar
datawiz23
Frequent Visitor
2 years ago
Solved

Rank DAX problem in Power BI

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)

) 

DAX
rank
  • Fowmy's avatar
    Fowmy
    2 years ago

    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

     



4 Replies

  • datawiz23's avatar
    datawiz23
    Frequent Visitor
    2 years ago

    sorry for the delay Fowmy 

    here is some data from the model

    yearcountryUniversityWorld Rank in Year
    2023AlbaniaPolytechnic University of Tirana100036.8
    2019AlgeriaBadji Mokhtar University – Annaba1023
    2022AlgeriaBadji Mokhtar University – Annaba1234
    2023AlgeriaBadji Mokhtar University – Annaba1528
    2021AlgeriaBlida 1 University1066
    2022AlgeriaBlida 1 University1251
    2023AlgeriaBlida 1 University1541
    2023AlgeriaÉcole Nationale Polytechnique100013.7
    2023AlgeriaÉcole Polytechnique d’Architecture et d’Urbanisme100013.8
    2019AlgeriaFerhat Abbas Sétif University 1871
    2020AlgeriaFerhat Abbas Sétif University 1652
    2021AlgeriaFerhat Abbas Sétif University 1527
    2022AlgeriaFerhat Abbas Sétif University 1536
    2023AlgeriaFerhat Abbas Sétif University 1426

    kind regards,

    Mark

    • Fowmy's avatar
      Fowmy
      Icon for Super User rankSuper User
      2 years ago

      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

       



      • datawiz23's avatar
        datawiz23
        Frequent Visitor
        2 years ago

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