Forum Discussion

Dimitris_Kats's avatar
Dimitris_Kats
Icon for Helper V rankHelper 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 ...
  • 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]
    )
     
OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper 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.