cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nantes44100 Regular Visitor
Regular Visitor

Rankx = struggling with duplicates values

Hi,

 

I looked a dozen of posts and webpages about how to create a rank with DAX, but I can't find the solution.

 

https://app.powerbi.com/groups/me/reports/e5ac9c36-b492-460a-974a-168797870f7d?ctid=2af43e5d-907a-49...

 

here is a simple example :

 

I have a dataset with some projects and some duplicates with the Details_Project column

 

ID_PROJECTPROJECT_NAMEDUE_TOSTATUSAREADISTANCEDETAILS_PROJECT
207492Project A31/12/2032In progress1800006649detail 1
155362Project AA30/06/2012Finished450008161detail 1
155362Project AA30/06/2012Finished450008161detail 2
126827Project B31/12/2019In progress35170478detail 1
126827Project B31/12/2019In progress35170478detail 2
126827Project B31/12/2019In progress35170478detail 3
126827Project B31/12/2019In progress35170478detail 4
126827Project B31/12/2019In progress35170478detail 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 !

 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Nantes44100 Regular Visitor
Regular Visitor

Re: Rankx = struggling with duplicates values

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])

 

1 REPLY 1
Highlighted
Nantes44100 Regular Visitor
Regular Visitor

Re: Rankx = struggling with duplicates values

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])