The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 method | Creation date | Creation time | Distribution method | Operation | User (UPN) | Workspace name |
2024-12-15 00:00:00 | 2024-12-15 17:13:48 | GetGroupUsersAsAdmin | test@test.com | General | ||
2024-12-15 00:00:00 | 2024-12-15 17:13:48 | GetGroupUsersAsAdmin | test@test.com | Microsoft Fabric Capacity Metrics | ||
2024-12-15 00:00:00 | 2024-12-15 17:13:48 | GetGroupUsersAsAdmin | test@test.com | Test | ||
2024-12-15 00:00:00 | 2024-12-15 17:15:25 | GetGroupsAsAdmin | test@test.com | Workspace Info Not Available | ||
2024-12-15 00:00:00 | 2024-12-15 17:16:11 | GetGroupsAsAdmin | test@test.com | Workspace Info Not Available | ||
2024-12-15 00:00:00 | 2024-12-15 17:17:02 | GetDatasetsInGroupAsAdmin | test@test.com | Workspace Info Not Available | ||
2024-12-15 00:00:00 | 2024-12-15 17:17:03 | GetDatasetsInGroupAsAdmin | test@test.com | Workspace Info Not Available | ||
2024-12-15 00:00:00 | 2024-12-15 17:17:22 | GetReportsInGroupAsAdmin | test@test.com | Workspace Info Not Available | ||
2024-12-15 00:00:00 | 2024-12-15 17:17:36 | GetDataflowsInGroupAsAdmin | test@test.com | Workspace Info Not Available | ||
2024-12-15 00:00:00 | 2024-12-15 17:17:37 | GetDataflowsInGroupAsAdmin | test@test.com | Workspace Info Not Available | ||
2024-12-15 00:00:00 | 2024-12-15 17:17:53 | GetGroupUsersAsAdmin | test@test.com | General | ||
2024-12-15 00:00:00 | 2024-12-15 17:17:53 | GetGroupUsersAsAdmin | test@test.com | Microsoft Fabric Capacity Metrics | ||
2024-12-15 00:00:00 | 2024-12-15 17:17:53 | GetGroupUsersAsAdmin | test@test.com | Test | ||
2024-12-15 00:00:00 | 2024-12-15 17:18:06 | GetGroupsAsAdmin | test@test.com | Workspace Info Not Available | ||
2024-12-15 00:00:00 | 2024-12-15 17:19:11 | GetGroupsAsAdmin | test@test.com | Workspace Info Not Available | ||
2024-12-15 00:00:00 | 2024-12-15 17:19:16 | GetDatasetsInGroupAsAdmin | test@test.com | Workspace Info Not Available | ||
2024-12-15 00:00:00 | 2024-12-15 17:19:17 | GetDatasetsInGroupAsAdmin | test@test.com | Workspace Info Not Available | ||
2024-12-15 00:00:00 | 2024-12-15 17:19:18 | GetDataflowsInGroupAsAdmin | test@test.com | Workspace Info Not Available | ||
2024-12-15 00:00:00 | 2024-12-15 17:19:18 | GetGroupUsersAsAdmin | test@test.com | General | ||
2024-12-15 00:00:00 | 2024-12-15 17:19:18 | GetGroupUsersAsAdmin | test@test.com | Microsoft Fabric Capacity Metrics | ||
2024-12-15 00:00:00 | 2024-12-15 17:19:18 | GetGroupUsersAsAdmin | test@test.com | Test | ||
2024-12-15 00:00:00 | 2024-12-15 17:19:18 | GetReportsInGroupAsAdmin | test@test.com | Workspace Info Not Available | ||
Power BI Web | 2024-12-16 00:00:00 | 2024-12-16 06:12:21 | Apps | ViewReport | test@test.com | General |
Power BI Web | 2024-12-16 00:00:00 | 2024-12-16 06:12:39 | Shared | ViewReport | test@test.com | Admin monitoring |
Power BI Web | 2024-12-16 00:00:00 | 2024-12-16 06:13:55 | Shared | ViewReport | test@test.com | Admin monitoring |
Power BI Web | 2024-12-16 00:00:00 | 2024-12-16 15:52:27 | Apps | ViewReport | test@test.com | General |
Power BI Web | 2024-12-16 00:00:00 | 2024-12-16 15:52:46 | Shared | ViewReport | test@test.com | Admin monitoring |
Solved! Go to Solution.
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:
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.
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:
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:
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:
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.
User | Count |
---|---|
15 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
24 | |
14 | |
13 | |
8 | |
8 |