Forum Discussion

TCavins's avatar
TCavins
Helper V
6 years ago

RANKX Help

I have a table with the following columns.   Person Site Manager IncidentID   I want my visualization to be: Person, Site, Manager, DISTINCTCOUNT(IncidentID), Rank(1 - whatever, based on the ...
Need Help
TCavins's avatar
TCavins
Helper V
6 years ago

That did not work. I'm trying the following Measure but it only works if Manager is not in the visualization.

 

Measure = IF(ISINSCOPE(MyTable[Manager]),RANKX(ALLSELECTED(MyTable),CALCULATE(DISTINCTCOUNT(MyTable[INCIDENTID])),,DESC,DENSE),RANKX(ALLSELECTED(MyTable[Person]),CALCULATE(DISTINCTCOUNT(MyTable[INCIDENTID])),,DESC,DENSE))

TCavins's avatar
TCavins
Helper V
6 years ago

Found another solution that is close but the rank numbers are off, especially if Manager is empty:

 

Measure 3 =
Var summry = SUMMARIZE(ALLSELECTED(MyTable),[Person],"DistCount",DISTINCTCOUNT(MyTable[INCIDENTID]))
var tmp = ADDCOLUMNS(summry,"RNK", RANKX(summry,[DistCount],,DESC,DENSE))
return
MAXX(FILTER(tmp,[Person]=SELECTEDVALUE(MyTable[Person])),[RNK])

 

Gives the following output. The second row should be rank 2 and the first row should be rank 1. I've blocked out any preson information. Columns are Person, manager, distinct count of IDs then 3 measure I'm trying for rank. Last column is the measure above.

 

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    Hi TCavins ,

     

    Try  to create a calculated column as below:

     

     

     

    Column =
VAR summry =
    SUMMARIZE (
        ALLSELECTED ( MyTable ),
        [Person],
        "DistCount", DISTINCTCOUNT ( MyTable[INCIDENTID] )
    )
RETURN
    RANKX ( summry, [DistCount],, DESC, DENSE )

     

     

     

    Best Regards,
    Kelly

    • TCavins's avatar
      TCavins
      Helper V
      6 years ago

      This isn't working for me.