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

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 ğŸ˜€