Forum Discussion
Community ChampionLEFT JOIN with multiple conditions
Hello, DAX-profies!
I have a user sessions log
User session could be started at one day and finished at the other
I need to match calendar days and quantity of uniques users who were active a this day
So, user_sessions table is:
| Session_id | User_id | Start_Session | End_Session |
| 1 | 1 | 24.10.2018 3:00 | 26.10.2018 17:00 |
| 2 | 1 | 27.10.2018 1:00 | 27.10.2018 3:00 |
| 3 | 2 | 15.10.2018 3:00 | 20.10.2018 19:00 |
| 4 | 3 | 04.10.2018 15:00 | 04.10.2018 15:01 |
| 5 | 3 | 25.10.2018 23:00 | 26.10.2018 2:00 |
It looked like good idea to first create table
Yeardays = CALENDAR(MIN(USER_SESSIONS[Start_Session]);MAX(USER_SESSIONS[End_Session]))
then to left join it with user_sessions using a couple of filters:
Table = NATURALLEFTOUTERJOIN(Yeardays;filter(USER_SESSIONS;USER_SESSIONS[Start_Session]<NEXTDAY(Yeardays[SessionDay]) && USER_SESSIONS[End_Session]>=Yeardays[SessionDay]))
Precisely, it doesnt work
A single value for column 'SessionDay' in table 'Yeardays2' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
I was trying variants but unsuccesfull :(
You can create a calculated table like this:
LogInsModified = SELECTCOLUMNS( GENERATE( LogIns, VAR St = INT( LogIns[Start_Session] ) VAR En = INT( LogIns[End_Session] ) RETURN CALENDAR( st, en ) ), "Session_id", [Session_id], "User_ID", [User_id], "Date", [Date] )Then relate it to the calendar table and use this simple measure:
Active Users = DISTINCTCOUNT( LogInsModified[User_ID] )
1 Reply
- LivioLanzo
Solution Sage
You can create a calculated table like this:
LogInsModified = SELECTCOLUMNS( GENERATE( LogIns, VAR St = INT( LogIns[Start_Session] ) VAR En = INT( LogIns[End_Session] ) RETURN CALENDAR( st, en ) ), "Session_id", [Session_id], "User_ID", [User_id], "Date", [Date] )Then relate it to the calendar table and use this simple measure:
Active Users = DISTINCTCOUNT( LogInsModified[User_ID] )