Forum Discussion
Calculated column for rank within group
Hi,
I have data including a unique session id, a timestamp for the different events within that session and their names. I would like to create a column that shows the rank of each event within the session, as shown below.
| Session_ID | Timestamp | Name | Rank |
| 1 | 22.05.2023 18:00 | a | 1 |
| 1 | 22.05.2023 18:10 | b | 2 |
| 2 | 22.05.2023 18:05 | b | 1 |
| 2 | 22.05.2023 18:07 | a | 2 |
I've tried a couple of different things, but the closest I've come is with this (Overview being the table name):
Rank within Session =
RANKX(
FILTER(
'Overview',
'Overview'[Session_ID]=EARLIER(Overview[Session_ID])
),
'Overview'[Timestamp]
)
which however gives the overall rank, disregarding session_id.
Any help would be greatly appreciated!
Hello Anonymous
Use the below Measure to populate the required result.
Rank Column =RANKX(FILTER(rankkx,rankkx[Session_ID] = EARLIER(rankkx[Session_ID]) &&(rankkx[Timestamp] < EARLIER(rankkx[Timestamp]) ||rankkx[Name] < EARLIER(rankkx[Name]))),rankkx[Timestamp],,ASC,Dense)let me know if this helps.If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
2 Replies
NaveenGandhiMemorable Member
Hello Anonymous
Use the below Measure to populate the required result.
Rank Column =RANKX(FILTER(rankkx,rankkx[Session_ID] = EARLIER(rankkx[Session_ID]) &&(rankkx[Timestamp] < EARLIER(rankkx[Timestamp]) ||rankkx[Name] < EARLIER(rankkx[Name]))),rankkx[Timestamp],,ASC,Dense)let me know if this helps.If this post helps, then please consider Accept it as the solution to help the others find it more quickly.Thanks a lot, NaveenGandhi, this seems to work 🙂