topic Session Duration in DAX Commands and Tips

Session Duration

Dimitris_Kats — Wed, 01 Jan 2025 15:17:07 GMT

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

Re: Session Duration

OwenAuger — Wed, 01 Jan 2025 22:01:43 GMT

Hi Dimitris_Kats

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.
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.

Re: Session Duration

Anonymous — Fri, 03 Jan 2025 03:08:49 GMT

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:

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.

Re: Session Duration

Dimitris_Kats — Wed, 08 Jan 2025 21:02:14 GMT

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

DATEDIFF(PreviousTime, CurrentTime, MINUTE)

)

Valid Session Time 2 =

IF(

[Time Difference 2] <= 30, -- 30 minutes threshold

[Time Difference 2],

)

Total Session Duration 2 =

SUMX(

VALUES(Audit[Creation time]),

[Valid Session Time 2]

)