Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Dimitris_Kats
Helper V
Helper V

Session Duration

‎01-01-2025 07:17 AM

Hello Dear members.

Happy new year!!

I need your help. I am connected to the Feature Usage and Adoption report in the admin monitoring workspace via a live connection in Power BI Desktop.

I am trying to calculate the session time per user per date, but I am encountering some challenges. Since there is a live connection, I can only create measures and not calculated columns.

There is an Audit table that includes the creation date, creation date-time, and operation. This table is linked to the User table, which contains the user email, and it is also connected to the Date table.

I want to calculate the session duration by determining the time difference between operations (unfortunately, there isn't a session ID available).

Could you please assist me? Below is a sample of the data.

Consumption methodCreation dateCreation timeDistribution methodOperationUser (UPN)Workspace name
 2024-12-15 00:00:002024-12-15 17:13:48 GetGroupUsersAsAdmintest@test.comGeneral
 2024-12-15 00:00:002024-12-15 17:13:48 GetGroupUsersAsAdmintest@test.comMicrosoft Fabric Capacity Metrics
 2024-12-15 00:00:002024-12-15 17:13:48 GetGroupUsersAsAdmintest@test.comTest
 2024-12-15 00:00:002024-12-15 17:15:25 GetGroupsAsAdmintest@test.comWorkspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:16:11 GetGroupsAsAdmintest@test.comWorkspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:17:02 GetDatasetsInGroupAsAdmintest@test.comWorkspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:17:03 GetDatasetsInGroupAsAdmintest@test.comWorkspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:17:22 GetReportsInGroupAsAdmintest@test.comWorkspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:17:36 GetDataflowsInGroupAsAdmintest@test.comWorkspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:17:37 GetDataflowsInGroupAsAdmintest@test.comWorkspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:17:53 GetGroupUsersAsAdmintest@test.comGeneral
 2024-12-15 00:00:002024-12-15 17:17:53 GetGroupUsersAsAdmintest@test.comMicrosoft Fabric Capacity Metrics
 2024-12-15 00:00:002024-12-15 17:17:53 GetGroupUsersAsAdmintest@test.comTest
 2024-12-15 00:00:002024-12-15 17:18:06 GetGroupsAsAdmintest@test.comWorkspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:19:11 GetGroupsAsAdmintest@test.comWorkspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:19:16 GetDatasetsInGroupAsAdmintest@test.comWorkspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:19:17 GetDatasetsInGroupAsAdmintest@test.comWorkspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:19:18 GetDataflowsInGroupAsAdmintest@test.comWorkspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:19:18 GetGroupUsersAsAdmintest@test.comGeneral
 2024-12-15 00:00:002024-12-15 17:19:18 GetGroupUsersAsAdmintest@test.comMicrosoft Fabric Capacity Metrics
 2024-12-15 00:00:002024-12-15 17:19:18 GetGroupUsersAsAdmintest@test.comTest
 2024-12-15 00:00:002024-12-15 17:19:18 GetReportsInGroupAsAdmintest@test.comWorkspace Info Not Available
Power BI Web2024-12-16 00:00:002024-12-16 06:12:21AppsViewReporttest@test.comGeneral
Power BI Web2024-12-16 00:00:002024-12-16 06:12:39SharedViewReporttest@test.comAdmin monitoring
Power BI Web2024-12-16 00:00:002024-12-16 06:13:55SharedViewReporttest@test.comAdmin monitoring
Power BI Web2024-12-16 00:00:002024-12-16 15:52:27AppsViewReporttest@test.comGeneral
Power BI Web2024-12-16 00:00:002024-12-16 15:52:46SharedViewReporttest@test.comAdmin monitoring



Message 1 of 4
552 Views
0
2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

‎01-02-2025 07:08 PM

Hi @Dimitris_Kats 

 

Thanks to OwenAuger for his great response to this thread.

Please allow me to add a possible solution below, i hope it helps.

Create the following measures:

OperationTime = MAX('Audit'[Creation time])
PreviousOperationTime = 
VAR CurrentUser = MAX('Audit'[User (UPN)])
VAR CurrentTime = MAX('Audit'[Creation time])
RETURN
CALCULATE(
    MAX('Audit'[Creation time]),
    FILTER(
        'Audit',
        'Audit'[User (UPN)] = CurrentUser &&
        'Audit'[Creation time] < CurrentTime
    )
)
SessionDuration = 
VAR PrevTime = [PreviousOperationTime]
VAR CurrTime = MAX('Audit'[Creation time])
RETURN
IF(
    ISBLANK(PrevTime),
    0,
    DATEDIFF(PrevTime, CurrTime, SECOND)
)
TotalSessionDuration = 
VAR VirtualTable = 
    ADDCOLUMNS(
        SUMMARIZE(
            Audit,
            Audit[User (UPN)],
            Audit[Creation date],
            Audit[Operation]
        ),
        "SessionDurationValue", [SessionDuration]
    )
RETURN
    SUMX(
        VirtualTable,
        [SessionDurationValue]
    )

 

Create a matirx visual:

vxianjtanmsft_0-1735873681161.png

 

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Session Duration.pbix
Message 3 of 4
379 Views
Dimitris_Kats
Helper V
Helper V

‎01-08-2025 01:02 PM

Thank you so much both for your help.
It was really valuable to me.

The measures that gave me the desired result are the following:

Time Difference 2 =
VAR CurrentTime = MAX(Audit[Creation time])
VAR PreviousTime =
    CALCULATE(
        MAX(Audit[Creation time]),
        FILTER(
            all('Audit'),
            RELATED(User[User (UPN)]) = SELECTEDVALUE(User[User (UPN)]) &&
            Audit[Creation time] < CurrentTime &&
            Audit[Creation date] = SELECTEDVALUE(Audit[Creation date])
        )
    )
RETURN
IF(
    ISBLANK(PreviousTime),
    0,
    DATEDIFF(PreviousTime, CurrentTime, MINUTE)
)
Valid Session Time 2 =
IF(
    [Time Difference 2] <= 30, -- 30 minutes threshold
    [Time Difference 2],
    0
)
Total Session Duration 2 =
SUMX(
    VALUES(Audit[Creation time]),
    [Valid Session Time 2]
)
 

View solution in original post

Message 4 of 4
234 Views
0
3 REPLIES 3
Dimitris_Kats
Helper V
Helper V

‎01-08-2025 01:02 PM

Thank you so much both for your help.
It was really valuable to me.

The measures that gave me the desired result are the following:

Time Difference 2 =
VAR CurrentTime = MAX(Audit[Creation time])
VAR PreviousTime =
    CALCULATE(
        MAX(Audit[Creation time]),
        FILTER(
            all('Audit'),
            RELATED(User[User (UPN)]) = SELECTEDVALUE(User[User (UPN)]) &&
            Audit[Creation time] < CurrentTime &&
            Audit[Creation date] = SELECTEDVALUE(Audit[Creation date])
        )
    )
RETURN
IF(
    ISBLANK(PreviousTime),
    0,
    DATEDIFF(PreviousTime, CurrentTime, MINUTE)
)
Valid Session Time 2 =
IF(
    [Time Difference 2] <= 30, -- 30 minutes threshold
    [Time Difference 2],
    0
)
Total Session Duration 2 =
SUMX(
    VALUES(Audit[Creation time]),
    [Valid Session Time 2]
)
 
Message 4 of 4
235 Views
0
Anonymous
Not applicable

‎01-02-2025 07:08 PM

Hi @Dimitris_Kats 

 

Thanks to OwenAuger for his great response to this thread.

Please allow me to add a possible solution below, i hope it helps.

Create the following measures:

OperationTime = MAX('Audit'[Creation time])
PreviousOperationTime = 
VAR CurrentUser = MAX('Audit'[User (UPN)])
VAR CurrentTime = MAX('Audit'[Creation time])
RETURN
CALCULATE(
    MAX('Audit'[Creation time]),
    FILTER(
        'Audit',
        'Audit'[User (UPN)] = CurrentUser &&
        'Audit'[Creation time] < CurrentTime
    )
)
SessionDuration = 
VAR PrevTime = [PreviousOperationTime]
VAR CurrTime = MAX('Audit'[Creation time])
RETURN
IF(
    ISBLANK(PrevTime),
    0,
    DATEDIFF(PrevTime, CurrTime, SECOND)
)
TotalSessionDuration = 
VAR VirtualTable = 
    ADDCOLUMNS(
        SUMMARIZE(
            Audit,
            Audit[User (UPN)],
            Audit[Creation date],
            Audit[Operation]
        ),
        "SessionDurationValue", [SessionDuration]
    )
RETURN
    SUMX(
        VirtualTable,
        [SessionDurationValue]
    )

 

Create a matirx visual:

vxianjtanmsft_0-1735873681161.png

 

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Session Duration.pbix
Message 3 of 4
380 Views
OwenAuger
Super User
Super User

‎01-01-2025 02:01 PM

Hi @Dimitris_Kats 

  1. How exactly do you want to define Session Duration, using your data as an example? I'm assuming you would want to allow for multiple sessions per user per day, so would need logic to exclude "gaps" between sessions.
  2. It is possible to convert the live connection to the Feature Usage and Adoption semantic model to a DirectQuery connection, which would give you more flexibility to add calculated columns or other tables if needed (I've done this before with this particular model). It does need to be enabled in the Admin Portal.
    See here.

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn
Message 2 of 4
469 Views

Helpful resources

Announcements
May PBI 25 Carousel

Power BI Monthly Update - May 2025

Check out the May 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.

View All