Rankx = struggling with duplicates values

[Anonymous]

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_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 !

[Anonymous · Accepted 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])