Calculated column for rank within group

[Anonymous]

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!

[Anonymous]

Thanks a lot, @NaveenGandhi, this seems to work 🙂

[NaveenGandhi · Accepted solution]

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.