First date/time grouped by value in joined table

[Anonymous]

Hi,

I have two tables that I've joined together. One contains user sessions of a database (sessionid, userid, logintime etc).
The other contains sql commands made in each sessions and so is more granular (sessionid, sqlcommandid, sqlcommandtime etc).
I've joined them on sessionid.
I want to find the difference in time between the first session time and the first sql command time by user.
I've figured out how to get the first session time:

First Session Date = 
CALCULATE (
    MIN ( SESSIONS[LOGINTIME] ),
    ALLEXCEPT ( SESSIONS, SESSIONS[USERid] )
)

What I'm struggling with is getting the first sqlcommand time for each user. The problem is the sql command time and the user id are in different tables, albeit joined ones.
I've tried merging the tables to get around this but it keeps failing so I'm looking for a dax solution. 
Bit of a tricky one I know! But any pointers would be most appreciated.

[Payeras_BI]

Hi @Anonymous ,

In case you didn't want to increase the size of your model by adding extra columns, you still could get your Average with a measure leveraging the relationship between the two tables.

[Anonymous · Accepted solution]

I've found a solution for finding the first command time:

So you need to create 3 columns in power bi

1st one to get the first query by session ID on the SQLcommand table

FirstQuery =VAR CSessionID = SQL[SESSION_ID]return
MINX(FILTER(ALL(SQL),SQL[SESSION_ID] = CSessionID),SQL[START_TIME])

2nd one to get the first query by session ID on the SESSIONS table:

FirstQueryLookup = LOOKUPVALUE(SQL[FirstQuery],SQL[SESSION_ID],SESSIONS[SESSION_ID])

3rd one to now get the first query by username:

FirstQuery =VAR CUsername = SESSIONS[USER_NAME]return
MINX(FILTER(ALL(SESSIONS),SESSIONS[USER_NAME] = CUsername), SESSIONS[FirstQueryLookup])

[Payeras_BI]

Hi @Anonymous ,

Is this what you are looking for?

First Login time by user = 
MIN(UserSessions[Logintime])

First SQL command time by user = 
MIN(SQLCommands[sqlcommandtime])

[Anonymous]

Sort of, though I needed it as a measure so that I can display the average difference between the two times (average by user). The above works but only as a table. I've found a fairly complex solution.