cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TCavins
Resolver I
Resolver I

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 distinct count in the table).

 

Rough data I'd like displayed in the table:

Bob, Site A, Manager 1, 50, 1

Steve, Site C, Mgr 2, 49, 2

Bob, Site A, Manager 2, 47, 3

 

Everything I try for RANKX shows 1 for the rank. I've tried Measures and Calculated Column. Any help?

5 REPLIES 5
amitchandak
Super User IV
Super User IV

rank as new column = rank(all(table),table[IncidentID])

 

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

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.

 

tempsnip.png

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

This isn't working for me.

 

DistCount.JPG

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors