topic Re: First date/time grouped by value in joined table in DAX Commands and Tips

First date/time grouped by value in joined table

Anonymous — Thu, 01 Jul 2021 09:50:54 GMT

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.

Re: First date/time grouped by value in joined table

Payeras_BI — Thu, 01 Jul 2021 14:20:29 GMT

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

Re: First date/time grouped by value in joined table

Anonymous — Thu, 01 Jul 2021 17:23:27 GMT

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.

Re: First date/time grouped by value in joined table

Anonymous — Thu, 01 Jul 2021 17:25:58 GMT

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

Re: First date/time grouped by value in joined table

Payeras_BI — Fri, 02 Jul 2021 08:42:58 GMT

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.