topic Re: How to Generate Unique Ranks in Power BI Table Visual in DAX Commands and Tips

How to Generate Unique Ranks in Power BI Table Visual

SBC — Wed, 06 Nov 2024 08:50:42 GMT

Hello Community,

I am working on a Power BI report where I need to generate unique ranks in a table visual. The ranking is based on a measure from one table (Table2) and a column from another table (Table1). Below is the detailed scenario:

Table1 contains the UserID column.
Table2 contains session data, and I have created a measure called [Sessions] that calculates the number of sessions for each user.
There is an established relationship between UserID in Table1 and the session data in Table2.

Requirements:

I want to rank users based on the [Sessions] measure in descending order, where the user with the highest session count gets rank 1.
If two or more users have the same session count, they should still receive different ranks (i.e., no ties).
The ranking should be dynamic and update based on filters applied in the report.
The ranking should be displayed in a table visual.
Sample Data:
- Table1 (UserID):
  
  UserID
  Alice
  Bob
  Carol
  Dave
  Eve
- Table2 (Sessions):
  - The [Sessions] measure calculates session duration or count for each user.
Expected Output Table visual:

UserID Sessions Rank_Sessions
Dave 60 1
Bob 45 2
Alice 30 3
Eve 30 4
Carol 20 5
As you can see, even though Alice and Eve have the same session count (30), they are assigned different ranks (3 and 4, respectively) to ensure uniqueness.
Current DAX Logic:
This is the current DAX logic I am using to generate ranks:
text
Rank_Sessions = IF( ISINSCOPE('Table1'[UserID]), RANKX( ALL('Table1'), [Sessions], , DESC, DENSE ), BLANK() )
Issue:
This formula works for generating ranks but does not handle ties well when multiple users have the same session count. I need help modifying this logic so that when two users have identical session counts, they are still assigned different ranks (i.e., no ties).Any suggestions or improvements would be greatly appreciated!Thanks,SBC

Re: How to Generate Unique Ranks in Power BI Table Visual

Anonymous — Wed, 06 Nov 2024 09:37:43 GMT

Hi SBC ,
Try by using this way like by replacing rank with rownumber, i hope it works

Re: How to Generate Unique Ranks in Power BI Table Visual

SBC — Wed, 06 Nov 2024 09:52:49 GMT

Hi Anonymous ,

thanks for responding. The session is actually a measure from one table2, and the user ID is from a different table, which is a column in Table 1. I'm having trouble applying the logic you provided.

Re: How to Generate Unique Ranks in Power BI Table Visual

Anonymous — Wed, 06 Nov 2024 11:18:30 GMT

Then you can merge the two tables on User ID and then apply the dax which i shared
or
else use the related function and try adding the sessions column into table 1 and then apply it will works for you (link for how to apply related : https://youtu.be/8YJyALHRKRY?si=kVrp91C110j6u34d)
Hope it will work for you
or else explain it clearly what you are not getting then i will help you

Re: How to Generate Unique Ranks in Power BI Table Visual

ThxAlot — Wed, 06 Nov 2024 20:36:40 GMT

UserID	Sessions	Rank_Sessions
Dave	60	1
Bob	45	2
Alice	30	3
Eve	30	4
Carol	20	5

topic Re: How to Generate Unique Ranks in Power BI Table Visual in DAX Commands and Tips

How to Generate Unique Ranks in Power BI Table Visual

Requirements:

Sample Data:

Expected Output Table visual:

Current DAX Logic:

Issue:

Re: How to Generate Unique Ranks in Power BI Table Visual

Re: How to Generate Unique Ranks in Power BI Table Visual

Re: How to Generate Unique Ranks in Power BI Table Visual

Re: How to Generate Unique Ranks in Power BI Table Visual