cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## DAX Calculated Column Sequence of Event Actions and Sessions

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.

Super User

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])
)
)

Proud to be a Super User!

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors