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. 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
Prior Year To Date Frustration
All: I have been battling the abiliy to display YTD and Prior YTD. My dataset is very simple - example is below: Date Value 4/5/2021 5 4/4/2021 5 4/3/2021 5 4/2/2021 5 My expressions are YTD: YTD = TOTALYTD(SUM('Sheet1'[Value]), 'Sheet1'[Date]) Prior Year to Date: LYTD = CALCULATE(Sheet1[YTD],(SAMEPERIODLASTYEAR(Sheet1[Date]))) If I add the date to my visual I am able to get the value I expect for each date. I cant figure out why the sum of Prior YTD has 605 and not 480. If I remove the individual date from the visual my numbers are incorrect. Very Frustrated - any help would be appreciated.
Reuse filter expressions ?
Hello, I am building a dashboard where I want to show daily/weekly/monthly KPis based on a date selection. Is there a way to reuse filter expressions I build for a measure in other ones? I have several measures (amount, quantity, target...) that should be filtered the same way. I would want to be able to have something like Weekly sales = Calculate([Sales measure],[Weekly filter expression]) Weekly sales target=Calculate([Target measure],[Weekly filter expression]) To facilitate the maintenance and update of my DAX expressions. Is there a way to do this?
