TOP N and RANKX with duplicated values

[PabloA]

Hello everyone!

I'm stuck trying to show on a card the name of the person who has received the most assigned visits.

This is due to the duplicate values of the RANKX function. If duplicate data did not occur in the database this problem would not occur and would display the name.

Rank = RANKX(ALL(example[OPERATOR]); SUMX(RELATEDTABLE(example); [Assigned]);;;DENSE)

Assigned = CALCULATE([DAX Status]; 'example'[STATUS] = "assigned visit" )

DAX Status = COUNT(example[STATUS])

If there are equal values in the database, how can I break that tie according to other criteria?

Each of them has a numeric ID

I really appreciate any suggestions and help.

Thanks.

[V-lianl-msft]

Hi @PabloA ,

Try to use FIRSTNONBLANK and LASTNONBLANK get the text value,then concatenate two text fields with CONCATENATE.

BEST = VAR FIRST = CALCULATE(FIRSTNONBLANK(EXAMPLE[operator],1),FILTER(EXAMPLE,[Rank]=1))
VAR LAST = CALCULATE(LASTNONBLANK(EXAMPLE[operator],1),FILTER(EXAMPLE,[Rank]=1 ))

RETURN CONCATENATE(CONCATENATE(FIRST,"/"),LAST)

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

[PabloA]

Hello, thanks for answering! I've tried it and it seems to work fine to show both, it's a good idea. The problem is that in the case that there are 3 as Ranking 1 I would have problems again. The ideal would be to break the tie and that there is only a number 1, is it possible?

[harshnathani]

Hi @PabloA ,

Try this. 

Assigned Visit =

var _b =

CONCATENATEX(

TOPN(

1,

SUMMARIZE(

'Table',

'Table'[ID],

'Table'[Status],

'Table'[Operator],

"CountAsV", CALCULATE(COUNT('Table'[Status]),FILTER('Table','Table'[Status] = "Assigned Visit"))

),

[CountAsV],

DESC

),

'Table'[Operator],

","

)

RETURN

_b

This will work incase you have n number of 1st Ranks.

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)