cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

First date/time grouped by value in joined table

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

4 REPLIES 4
Payeras_BI
Solution Sage
Solution Sage

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.

Payeras_BI_0-1625215117026.png

 

 

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain
Anonymous
Not applicable

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
Solution Sage
Solution Sage

Hi @Anonymous ,

Is this what you are looking for?

Payeras_BI_0-1625148915255.png

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

Payeras_BI_1-1625149026390.png

 

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain
Anonymous
Not applicable

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors