Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I looked a dozen of posts and webpages about how to create a rank with DAX, but I can't find the solution.
here is a simple example :
I have a dataset with some projects and some duplicates with the Details_Project column
ID_PROJECT | PROJECT_NAME | DUE_TO | STATUS | AREA | DISTANCE | DETAILS_PROJECT |
207492 | Project A | 31/12/2032 | In progress | 180000 | 6649 | detail 1 |
155362 | Project AA | 30/06/2012 | Finished | 45000 | 8161 | detail 1 |
155362 | Project AA | 30/06/2012 | Finished | 45000 | 8161 | detail 2 |
126827 | Project B | 31/12/2019 | In progress | 35170 | 478 | detail 1 |
126827 | Project B | 31/12/2019 | In progress | 35170 | 478 | detail 2 |
126827 | Project B | 31/12/2019 | In progress | 35170 | 478 | detail 3 |
126827 | Project B | 31/12/2019 | In progress | 35170 | 478 | detail 4 |
126827 | Project B | 31/12/2019 | In progress | 35170 | 478 | detail 5 |
I want to create a visualization for status projects "Finished" and get a rank based on distance or area (user has to choice with a segment)
I created 2 measures, but the ranks are not working properly because of the duplicates I think.
I maybe have to combine Rankx with Summarize ?
Please, could you check my test online ?
Thanks a lot !
Solved! Go to Solution.
I found the solution by using SUMMARIZE !
so if in your dataset you have some duplicates, but you want to rank on a unique row you can use :
RankingAREA_Summarize_2 = RANKX( ALLSELECTED('Rank Exercice'); CALCULATE(sumx(SUMMARIZE('Rank Exercice';'Rank Exercice'[ID_PROJECT];'Rank Exercice'[AREA]);'Rank Exercice'[AREA]));;DESC;Dense )
In my case, I wanted to rank by AREA
same thing to order by DISTANCE.
And in case, if you want your users to select via a segment which rank to display, just use :
RankingCHOICE = VAR Unit = SELECTEDVALUE(USER_CHOICE[LIB]) RETURN SWITCH(TRue(); Unit = "distance";'DAX_MESURES'[RankingDISTANCE_Summarize_2]; Unit = "area";'DAX_MESURES'[RankingAREA_Summarize_2])
I found the solution by using SUMMARIZE !
so if in your dataset you have some duplicates, but you want to rank on a unique row you can use :
RankingAREA_Summarize_2 = RANKX( ALLSELECTED('Rank Exercice'); CALCULATE(sumx(SUMMARIZE('Rank Exercice';'Rank Exercice'[ID_PROJECT];'Rank Exercice'[AREA]);'Rank Exercice'[AREA]));;DESC;Dense )
In my case, I wanted to rank by AREA
same thing to order by DISTANCE.
And in case, if you want your users to select via a segment which rank to display, just use :
RankingCHOICE = VAR Unit = SELECTEDVALUE(USER_CHOICE[LIB]) RETURN SWITCH(TRue(); Unit = "distance";'DAX_MESURES'[RankingDISTANCE_Summarize_2]; Unit = "area";'DAX_MESURES'[RankingAREA_Summarize_2])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |