Forum Discussion

Dimitris_Kats's avatar
Dimitris_Kats
Helper V
1 year ago
Solved

Session Duration

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 GetGroupUsersAsAdmin[email protected]General
 2024-12-15 00:00:002024-12-15 17:13:48 GetGroupUsersAsAdmin[email protected]Microsoft Fabric Capacity Metrics
 2024-12-15 00:00:002024-12-15 17:13:48 GetGroupUsersAsAdmin[email protected]Test
 2024-12-15 00:00:002024-12-15 17:15:25 GetGroupsAsAdmin[email protected]Workspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:16:11 GetGroupsAsAdmin[email protected]Workspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:17:02 GetDatasetsInGroupAsAdmin[email protected]Workspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:17:03 GetDatasetsInGroupAsAdmin[email protected]Workspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:17:22 GetReportsInGroupAsAdmin[email protected]Workspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:17:36 GetDataflowsInGroupAsAdmin[email protected]Workspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:17:37 GetDataflowsInGroupAsAdmin[email protected]Workspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:17:53 GetGroupUsersAsAdmin[email protected]General
 2024-12-15 00:00:002024-12-15 17:17:53 GetGroupUsersAsAdmin[email protected]Microsoft Fabric Capacity Metrics
 2024-12-15 00:00:002024-12-15 17:17:53 GetGroupUsersAsAdmin[email protected]Test
 2024-12-15 00:00:002024-12-15 17:18:06 GetGroupsAsAdmin[email protected]Workspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:19:11 GetGroupsAsAdmin[email protected]Workspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:19:16 GetDatasetsInGroupAsAdmin[email protected]Workspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:19:17 GetDatasetsInGroupAsAdmin[email protected]Workspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:19:18 GetDataflowsInGroupAsAdmin[email protected]Workspace Info Not Available
 2024-12-15 00:00:002024-12-15 17:19:18 GetGroupUsersAsAdmin[email protected]General
 2024-12-15 00:00:002024-12-15 17:19:18 GetGroupUsersAsAdmin[email protected]Microsoft Fabric Capacity Metrics
 2024-12-15 00:00:002024-12-15 17:19:18 GetGroupUsersAsAdmin[email protected]Test
 2024-12-15 00:00:002024-12-15 17:19:18 GetReportsInGroupAsAdmin[email protected]Workspace Info Not Available
Power BI Web2024-12-16 00:00:002024-12-16 06:12:21AppsViewReport[email protected]General
Power BI Web2024-12-16 00:00:002024-12-16 06:12:39SharedViewReport[email protected]Admin monitoring
Power BI Web2024-12-16 00:00:002024-12-16 06:13:55SharedViewReport[email protected]Admin monitoring
Power BI Web2024-12-16 00:00:002024-12-16 15:52:27AppsViewReport[email protected]General
Power BI Web2024-12-16 00:00:002024-12-16 15:52:46SharedViewReport[email protected]Admin monitoring



  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

  • Dimitris_Kats's avatar
    Dimitris_Kats
    1 year ago

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

3 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    1 year ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

     

  • Dimitris_Kats's avatar
    Dimitris_Kats
    Helper V
    1 year ago

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