Calculated column to group rows

[Anonymous]

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.

MessageType	Name	LoginTime	LogoffTime	Grouping
2	PR	19/11/2021 2:07	19/11/2021 2:12	1
2	PR	19/11/2021 2:12	19/11/2021 5:50	1
2	PR	19/11/2021 5:50	19/11/2021 13:20	1
2	PR	19/11/2021 13:20	19/11/2021 19:17	1
2	SENGINEER	19/11/2021 19:17	19/11/2021 19:18	1
2	PR	19/11/2021 19:18	20/11/2021 16:06	1
2	PR	20/11/2021 18:04	20/11/2021 18:43	2
2	PR	20/11/2021 19:35	21/11/2021 1:43	3

[v-yanjiang-msft · Accepted solution]

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]

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]

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

[Fowmy]

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

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]

@Anonymous 


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

[Anonymous]

This would be the desired outcome:

https://docs.google.com/spreadsheets/d/1xm3ZbH8-PQGZvpbZT7OhgEpc-iWdTsjA/edit?usp=sharing&ouid=11552...