Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ganapathi
Frequent Visitor

Distinct Rank for each criteria

Hi All,

 

i would like to create Distinct Rank for each employee code seperately based on score as shown in the below picture. Rank should be distinct. For Instance, if the score is same for two lines rank should not be 1 and 1, it should be 1 and 2.

 

Distinct Rank.JPG

1 ACCEPTED SOLUTION
Twan
Advocate IV
Advocate IV

This is difficult since the built in RANKX function does not have an option to handle ties this way.  It would reutrn ties with non distinct numbers and would have similar values to your Actual Rank column.

 

One way to get around this limitation is to modify the Score data so that all values are unique.  This can be done by adding a very small random number to the score data.  The number would be so small that it would not change the score in any meaningful way but would allow the numbers to be unique so that ties are handled correctly when using the RANX function.

 

I would add a calculated column called Score Random:

Score Random = (RAND()*0.000001) + 'Table'[Score]

Then add a column that ranks based on the Score Random column:

Rank = RANKX(FILTER('Table', 'Table'[Employee Code] = EARLIER('Table'[Employee Code])), 'Table'[Score Rand],,DESC,Skip)

 

I ended up with this table as a result:

 

Rank Distinct.PNG

View solution in original post

5 REPLIES 5
Twan
Advocate IV
Advocate IV

This is difficult since the built in RANKX function does not have an option to handle ties this way.  It would reutrn ties with non distinct numbers and would have similar values to your Actual Rank column.

 

One way to get around this limitation is to modify the Score data so that all values are unique.  This can be done by adding a very small random number to the score data.  The number would be so small that it would not change the score in any meaningful way but would allow the numbers to be unique so that ties are handled correctly when using the RANX function.

 

I would add a calculated column called Score Random:

Score Random = (RAND()*0.000001) + 'Table'[Score]

Then add a column that ranks based on the Score Random column:

Rank = RANKX(FILTER('Table', 'Table'[Employee Code] = EARLIER('Table'[Employee Code])), 'Table'[Score Rand],,DESC,Skip)

 

I ended up with this table as a result:

 

Rank Distinct.PNG

Anonymous
Not applicable

How would you get this rank to skip all instances of lower values. i.e. display only the rank for the highest score for each employee?

 

Hi  - think this is my first post!

 

Is there a way to expand this to two criteria? I have a list of reasons that repeat for different customers in the same table, so the ranking in my model needs to be rank by customer by reason by number of reasons

 

hope this makes sense, can provide sample data if needed!

 

Thank you 🙂

Steve

Yeah it worked and meet my expectation. Thanks a lot!!

KHorseman
Community Champion
Community Champion

I'm not sure I understand how you intend for this to work. Why is each employee listed more than once with different scores, and what do you want to rank them against? It looks like you're ranking each score against the employee. Are you trying to rank each employee against every other employee? If so you're going to need to pick how you want to treat the multiple scores. Do you want the employee ranked based on their highest score only?  This looks like a confusing data model and I'm not sure it actually represents what you expect it to represent. What is your formula for that Actual Rank column?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.