Rank measure base on multiple columns and top 1 , top2 calculation

[Cris22]

Good afternoon,

Please, I would appreatiate some help. I have the following:

I would like to get the proper rank for the parameter based on LAP and DRIVER considering the segmentation. I tried with the following measures but none of them are working:

Option 1) RANK = RANKX(ALLSELECTED(Data[LAP];Data[DRIVER]);[PARAMETER];;DESC;Dense)

Option 2) RANK = RANKX(ALLEXCEPT(Data;Data[LAP];Data[DRIVER]);[PARAMETER];;DESC;Dense)

With this ranking, I want to get the top1 PARAMETER value (maximum) and top2 PARAMETER value (second maximum)   for each DRIVER.

Thank you very much for your help.

[Jihwan_Kim]

Hi, 

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

parameter measure: = 
SUM( data[parameter] )

RANK function (DAX) - DAX | Microsoft Learn

parameter rank: = 
RANK (
    DENSE,
    ALL ( data[lap], data[driver] ),
    ORDERBY ( [parameter measure:], DESC ),
    ,
    PARTITIONBY ( data[driver] )
)

top 2 by driver: = 
IF ( [parameter rank:] <= 2, [parameter measure:] )

[Cris22]

Hello, Thank you Jihwan_Kim for your answer.

I tried with the ranking you told me, but I still has problems. I have complete the phbix file you did to show you my problem.

 I am trying to attached the phbix that I completed, but I can't.

I think, there is a problem on the code because it has to take into account the segmentation of the year (2022,2023), track (test, test 1,..) and session (example:R1,R2,..).

 Thank you

[Jihwan_Kim]

Hi,

please share the link of your sample pbix file (onedrive link, dropbox link, or others), and then I can try to look into it.

Thank you.

[Cris22]

Hello, I am not sure if it can be openend by you in here: 

Cris22v2.pbix

I share it with you by email as well.

Thank you