Forum Discussion

PabloA's avatar
PabloA
New Member
6 years ago

TOP N and RANKX with duplicated values

  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...
Need Help
V-lianl-msft's avatar
V-lianl-msft
Community Support
6 years ago

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's avatar
PabloA
New Member
6 years ago

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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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)