Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a table below, with the login time and logoff time, as well as the previous rows logoff time.
I need to group the rows together based on the condition that the difference between the previous logofftime and logintime is not more than 20 mins. How can I do that to achieve the desired output?
Thanks.
Prev LogoffTime | StatsData.LoginTime | StatsData.LogoffTime | Desired output |
18/11/2021 3:11 | 18/11/2021 4:53 | 1 | |
18/11/2021 4:53 | 18/11/2021 4:54 | 18/11/2021 9:39 | 1 |
18/11/2021 9:39 | 18/11/2021 9:40 | 18/11/2021 15:57 | 1 |
18/11/2021 15:57 | 18/11/2021 16:47 | 18/11/2021 18:39 | 2 |
18/11/2021 18:39 | 18/11/2021 21:39 | 18/11/2021 22:26 | 3 |
18/11/2021 22:26 | 19/11/2021 2:07 | 19/11/2021 2:12 | 4 |
19/11/2021 2:12 | 19/11/2021 2:12 | 19/11/2021 5:50 | 5 |
19/11/2021 5:50 | 19/11/2021 5:50 | 19/11/2021 13:20 | 5 |
19/11/2021 13:20 | 19/11/2021 13:20 | 19/11/2021 19:17 | 5 |
19/11/2021 19:17 | 19/11/2021 19:18 | 20/11/2021 16:06 | 5 |
20/11/2021 16:06 | 20/11/2021 18:04 | 20/11/2021 18:43 | 6 |
20/11/2021 18:43 | 20/11/2021 19:35 | 21/11/2021 1:43 | 7 |
21/11/2021 1:43 | 21/11/2021 2:08 | 21/11/2021 14:08 | 8 |
21/11/2021 14:08 | 21/11/2021 14:08 | 21/11/2021 16:14 | 8 |
21/11/2021 16:14 | 21/11/2021 16:41 | 21/11/2021 16:46 | 9 |
21/11/2021 16:46 | 21/11/2021 17:36 | 22/11/2021 0:39 | 10 |
22/11/2021 0:39 | 22/11/2021 1:00 | 22/11/2021 9:15 | 11 |
Solved! Go to Solution.
@Anonymous , The logic of 20 Min is not very clear to me. But a new column like
new column =
var _previous = maxx(filter(Table, [StatsData.LogoffTime] < earlier([StatsData.LogoffTime]) ),[StatsData.LogoffTime])
var _diff = Quotient(datediff(_previous, [StatsData.LogoffTime], minute),20)
return
if(isblank(_diff),1,_diff)
@Anonymous , The logic of 20 Min is not very clear to me. But a new column like
new column =
var _previous = maxx(filter(Table, [StatsData.LogoffTime] < earlier([StatsData.LogoffTime]) ),[StatsData.LogoffTime])
var _diff = Quotient(datediff(_previous, [StatsData.LogoffTime], minute),20)
return
if(isblank(_diff),1,_diff)
Hi Amit,
Thanks for your reply. The purpose of 20 mins is because all sessions within 20 minutes of one another will be considered as a single session.
As i have to filter out the AirlineGroupName and MessageType in order to get the table above, how should I get the output as a measure?
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |