Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Hello2000
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
1aa2023-01-01 13:12:11login
1aa2023-01-01 14:12:10buy
1bb2023-01-02 11:12:10page
2cc2023-01-01 10:11:01login
2gg2023-01-03 11:12:11logout
2gg2023-01-03 13:11:03click
2gg2023-01-03 14:10:07logout

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
1aa2023-01-01 13:12:11login11
1aa2023-01-01 14:12:10buy21
1bb2023-01-02 11:12:10page12
2cc2023-01-01 10:11:01login11
2gg2023-01-03 11:12:11logout12
2gg2023-01-03 13:11:03click22
2gg2023-01-03 14:10:07logout32



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=10399...

1 REPLY 1
rubayatyasmin
Super 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])
)
)

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors