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 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.

Need Help

3 Replies

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