Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a calculated table with the following fields representing log in sessions.
Im trying to group the rows together if:
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 |
Solved! Go to 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.
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.
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] ) ) )
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
@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 )
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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:
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 😀
@Anonymous
Not quite clear, could you show the calculation In Excel and attach the file using Google or One Drive link here?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
This would be the desired outcome:
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |