LEFT JOIN with multiple conditions

[az38]

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 😞

[LivioLanzo · Accepted solution]

@az38

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