Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculated column to group rows

I have a calculated table with the following fields representing log in sessions. 

 

Im trying to group the rows together if:

  • They have the same name ie PR
  • The time between the previous log out and current log in is not more than 20 minutes.

For example, in the first row, PR log into the workstation on 19/11, from 2:07:57 am to 2:12:13 pm. In the second row, PR logged into the workstation on 19/11, from 2:12:19 am to 5:50:28 pm.  Since the time between the first logout and second login is only 6 seconds (2:12:19 - 2:12:13), they should be grouped together.

 

The desired output could be a calculated column named grouping. This way I can create a measure to group by Grouping and Name and calculate the duration of each group.

 

MessageTypeNameLoginTimeLogoffTimeGrouping
2PR19/11/2021 2:0719/11/2021 2:121
2PR19/11/2021 2:1219/11/2021 5:501
2PR19/11/2021 5:5019/11/2021 13:201
2PR19/11/2021 13:2019/11/2021 19:171
2SENGINEER19/11/2021 19:1719/11/2021 19:181
2PR19/11/2021 19:1820/11/2021 16:061
2PR20/11/2021 18:0420/11/2021 18:432
2PR20/11/2021 19:3521/11/2021 1:433
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    According to your description, here's my solution, I create four measures.

    Mins Between Logoff (mins) = 
IF (
    MAX ( 'Table'[Rank] ) = 1,
    "",
    CALCULATE (
        DATEDIFF (
            MAXX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Rank]
                        = MAX ( 'Table'[Rank] ) - 1
                        && 'Table'[Name] = MAX ( 'Table'[Name] )
                ),
                'Table'[LogoffTime]
            ),
            MAX ( 'Table'[LoginTime] ),
            MINUTE
        )
    )
)

    Duration (mins) = DATEDIFF(MAX('Table'[LoginTime]),MAX('Table'[LogoffTime]),MINUTE)
    SessionID = 
CALCULATE (
    COUNT ( 'Table'[Name] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Name] = MAX ( 'Table'[Name] )
            && [Mins Between Logoff (mins)] >= 20
            && 'Table'[Rank] <= MAX ( 'Table'[Rank] )
    )
)

    Total Duration per session (mins) =
VAR _Add =
    ADDCOLUMNS ( ALL ( 'Table' ), "sessionID", [SessionID] )
VAR _Add2 =
    ADDCOLUMNS (
        _Add,
        "Duration",
            SUMX (
                FILTER (
                    _Add,
                    [Name] = EARLIER ( [Name] )
                        && [sessionID] = EARLIER ( [sessionID] )
                ),
                [Duration (mins)]
            )
    )
RETURN
    SUMX (
        FILTER (
            _Add2,
            [Name] = MAX ( 'Table'[Name] )
                && [Rank] = MAX ( 'Table'[Rank] )
        ),
        [Duration]
    )

    Get the expected result.

    I attach my sample below to help you understanding.

    Best Regards,
    Community Support Team _ kalyj

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

7 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    4 years ago

    Anonymous 

    Not sure about how the groupings should work, I created a new column to get the difference in Minuits. Please provide an extended example:

    Group = 

VAR __CURRENTLOGIN = Table6[LoginTime]
VAR __PREVIOUSLOGIN = 
    CALCULATE(
        MAX( Table6[LogoffTime] ),
        ALLEXCEPT(Table6 , Table6[Name] ),
        Table6[LoginTime] < __CURRENTLOGIN 
    )
RETURN
DATEDIFF(__PREVIOUSLOGIN , __CURRENTLOGIN, MINUTE )

     



    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for your response. The context is that the duration between the login times are used to charge the user. 

       

      If the time between two sessions (row) is less than 20 minutes, the two session will count as one and the total duration of both session will be summed up. So for example looking at just the first 2 rows:

      • the user logs on to the workstation from 2:07:57 am to 2:12:13 pm
      • then the same user logs on again from 2:12:19 am to 5:50:28 pm
      • The user will be charged for a duration of 6h 45 mins.

       

      Thus, I would need a table where I can group the rows that are considered the same logon sessions together and sum up their durations. 

       

      Any help would be appreciated. Thanks 😀

      • Fowmy's avatar
        Fowmy
        Super User
        4 years ago

        Anonymous 


        Not quite clear, could you show the calculation In Excel and attach the file using Google or One Drive link here?

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    According to your description, here's my solution, I create four measures.

    Mins Between Logoff (mins) = 
IF (
    MAX ( 'Table'[Rank] ) = 1,
    "",
    CALCULATE (
        DATEDIFF (
            MAXX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Rank]
                        = MAX ( 'Table'[Rank] ) - 1
                        && 'Table'[Name] = MAX ( 'Table'[Name] )
                ),
                'Table'[LogoffTime]
            ),
            MAX ( 'Table'[LoginTime] ),
            MINUTE
        )
    )
)

    Duration (mins) = DATEDIFF(MAX('Table'[LoginTime]),MAX('Table'[LogoffTime]),MINUTE)
    SessionID = 
CALCULATE (
    COUNT ( 'Table'[Name] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Name] = MAX ( 'Table'[Name] )
            && [Mins Between Logoff (mins)] >= 20
            && 'Table'[Rank] <= MAX ( 'Table'[Rank] )
    )
)

    Total Duration per session (mins) =
VAR _Add =
    ADDCOLUMNS ( ALL ( 'Table' ), "sessionID", [SessionID] )
VAR _Add2 =
    ADDCOLUMNS (
        _Add,
        "Duration",
            SUMX (
                FILTER (
                    _Add,
                    [Name] = EARLIER ( [Name] )
                        && [sessionID] = EARLIER ( [sessionID] )
                ),
                [Duration (mins)]
            )
    )
RETURN
    SUMX (
        FILTER (
            _Add2,
            [Name] = MAX ( 'Table'[Name] )
                && [Rank] = MAX ( 'Table'[Rank] )
        ),
        [Duration]
    )

    Get the expected result.

    I attach my sample below to help you understanding.

    Best Regards,
    Community Support Team _ kalyj

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you so much for this.

       

      One question I have for SessionID, why is max used in the filter. Does the max take the row value that im evaluating ie 1 value only?

      SessionID = 
CALCULATE (
    COUNT ( 'Table'[Name] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Name] = MAX ( 'Table'[Name] )
            && [Mins Between Logoff (mins)] >= 20
            && 'Table'[Rank] <= MAX ( 'Table'[Rank] )
    )
)

       

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Community Support
        4 years ago

        Hi Anonymous ,

        MAX be used in a measure can return the current value at the same row in a visual.

        Best Regards,
        Community Support Team _ kalyj