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):UserIDAliceBobCarolDaveEveTable2 (Sessions):The [Sessions] measure calculates session duration or count for each user.Expected Output Table visual: UserIDSessionsRank_SessionsDave 60 1Bob452Alice303Eve304Carol205As 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:textRank_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

Helper III

1 year ago

Solved

How to Generate Unique Ranks in Power BI Table Visual

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