DAX Calculated Column Sequence of Event Actions and Sessions

Hello2000 — Wed, 19 Jul 2023 13:24:11 GMT

I have dataset with users actions logs. We have next data collection logic - every user have it own user_id and we record log of event actions during user session.

user_id session_id dateTime event

1	aa	2023-01-01 13:12:11	login
1	aa	2023-01-01 14:12:10	buy
1	bb	2023-01-02 11:12:10	page
2	cc	2023-01-01 10:11:01	login
2	gg	2023-01-03 11:12:11	logout
2	gg	2023-01-03 13:11:03	click
2	gg	2023-01-03 14:10:07	logout

The main goal is prepare output and add 2 calculated columns: 1. with events action sequence during every user session and 2. with session sequence during all user lifetime by SQL.

Expected output:

user_id session_id dateTime event event_seq session_seq

1	aa	2023-01-01 13:12:11	login	1	1
1	aa	2023-01-01 14:12:10	buy	2	1
1	bb	2023-01-02 11:12:10	page	1	2
2	cc	2023-01-01 10:11:01	login	1	1
2	gg	2023-01-03 11:12:11	logout	1	2
2	gg	2023-01-03 13:11:03	click	2	2
2	gg	2023-01-03 14:10:07	logout	3	2

What was done from my side:
1. Successfully create column with events action sequence during session

Event Action Sequence = COUNTROWS ( FILTER ( CALCULATETABLE ( Sheet1, ALLEXCEPT (Sheet1, Sheet1[session_id]) ), Sheet1[Date] < EARLIER ( Sheet1[Date] ) || ( Sheet1[Date] = EARLIER ( Sheet1[Date] ) && Sheet1[Time Action] <= EARLIER ( Sheet1[Time Action] ) ) ) )

2. Sessions Sequence Order By each user - calculated column not prepared - I stucked on it. Only prepared time for each session when it started (minimum time for each session)

Min Session DateTime = CALCULATE( MIN(Sheet1[dateTime]), ALLEXCEPT(Sheet1,Sheet1[session_id]) )

So the main help request, how to solve this and create one more column with sessions sequene number orderd ascending by timestamp for each user.

Sharing link to my pbix file https://drive.google.com/file/d/1807l9E07oNJv9l5rKWXKxDoc6rniyiYG/view?usp=sharing
And sharing link to my sample: https://docs.google.com/spreadsheets/d/1bb7VId8lJ-NQkF43YS739kfc5Ur-1cGy/edit?usp=sharing&ouid=103990807236416334574&rtpof=true&sd=true

Re: DAX Calculated Column Sequence of Event Actions and Sessions

rubayatyasmin — Wed, 19 Jul 2023 15:22:26 GMT

Hi, Hello2000

First, create a calculated column for the session sequence:

Session Sequence =
RANKX(
FILTER(
ALL(Sheet1),
Sheet1[user_id] = EARLIER(Sheet1[user_id])
),
Sheet1[dateTime],
,
ASC,
Dense
)

Next, create a calculated column for the event sequence within each session:

Event Sequence =
CALCULATE(
COUNTROWS(Sheet1),
FILTER(
ALL(Sheet1),
Sheet1[user_id] = EARLIER(Sheet1[user_id]) &&
Sheet1[session_id] = EARLIER(Sheet1[session_id]) &&
Sheet1[dateTime] <= EARLIER(Sheet1[dateTime])
)
)

topic Re: DAX Calculated Column Sequence of Event Actions and Sessions in DAX Commands and Tips

DAX Calculated Column Sequence of Event Actions and Sessions

Re: DAX Calculated Column Sequence of Event Actions and Sessions