Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Super User
Super User

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
Super User
Super User

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors