Forum Discussion
Ranking a table based on 2 dynamic selected years using Max function
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 !
4 Replies
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.
Hi Anonymous !
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!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.
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 !
