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...
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

     



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