Ranking a table based on 2 dynamic selected years using Max function

[Ngh25]

Hello @Ashish_Mathur 

I am trying to rank my Operations table based on 1 or more dynamic years chosen by the user from my Year slicer. 
This is my Operations table based on 2023 selection : 

Lab;Date;Nb_test; Rank;Rank Y-1
France;2023;5000;1;2
US;2023;602;4;5
INDIA;2023;1200;3;4

CHINA;2023;4300;2;1
BRAZIL;2023;338;5;3

2022 selection :

France;2022;4271;2;2
US;2022;708;5;5
INDIA;2022;2437;4;3

CHINA;2022;4980;1;1
BRAZIL;2022;2600;3;4

I want to create a measure that ensures both of the cases ( 1 or 2 selected values) and calculates the max value between both of the selected years then does the ranking this way : 

Lab	Lims request creation date	Max (Nb tests [2023 - 2022])	Max (Nb tests [2022 - 2021])	Ranking [2023 - 2022]	Ranking [2022 - 2021]
France	[2023 - 2022]	13230	10763	1	1
US	[2023 - 2022]	9302	8949	2	2
INDIA	[2023 - 2022]	7680	7324	4	3
CHINA	[2023 - 2022]	6002	6002	5	5
BRAZIL	[2023 - 2022]	8338	6366	3	4

If anyone has a recommendation on how to achieve it, please feel free to comment below 🙂 
Thanks !

 

[Ngh25]

Hi @v-xinruzhu-msft  again and thank you for your answer !
1-Nb test is a measure not a column so I can"t use it directly this way! 
2- I don't see the filter on the date range here, it's only taking the max value of nb tests but the filtrer doesn't work. I did it this way : 

Max_value = CALCULATE(MAX(Nb_tests),ALLSELECTED('Operations'),'Operations'[lab] in VALUES ('Operations'[lab]))

Rank = RANKX(ALLSELECTED('Operations'[lab]),[Max_value],,DESC)
I need to use he date column from Operations based on the selected values of the calandar slicer, but I can"t achieve it correctly ! with 1 parameter it work successfully, but here, it doesn't !

[Ngh25]

Hi @v-xinruzhu-msft ! 
thank you for your answer but it doesn't work for me! 
The issue here, is that I am  working in direct query mode, so I can't add tables or more links in the data model, the only way I have is to do it by creating a measure! 

[v-xinruzhu-msft]

Hi @Ngh25 

It will not affect it, if you cannot create the table, you can use the measures I offered directly, it can work.

Max_value = CALCULATE(MAX('Table'[Nb_test]),ALLSELECTED('Table'),'Table'[Lab] in VALUES('Table'[Lab]))

Rank = RANKX(ALLSELECTED('Table'[Lab]),[Max_value],,DESC)

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xinruzhu-msft]

Hi @Ngh25 

You can refer to the following sample.

Sample data 

1.You can create a year table, then create a relationship between tables.

2.Create a max measure

Max_value =
CALCULATE (
    MAX ( 'Table'[Nb_test] ),
    ALLSELECTED ( 'Table' ),
    'Table'[Lab] IN VALUES ( 'Table'[Lab] )
)

3.Create a rank measure

Rank = RANKX(ALLSELECTED('Table'[Lab]),[Max_value],,DESC)

4.Put the following fields to matrix

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.