Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Index/Group rows based on condition

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 LogoffTimeStatsData.LoginTimeStatsData.LogoffTimeDesired output
 18/11/2021 3:1118/11/2021 4:531
18/11/2021 4:5318/11/2021 4:5418/11/2021 9:391
18/11/2021 9:3918/11/2021 9:4018/11/2021 15:571
18/11/2021 15:5718/11/2021 16:4718/11/2021 18:392
18/11/2021 18:3918/11/2021 21:3918/11/2021 22:263
18/11/2021 22:2619/11/2021 2:0719/11/2021 2:124
19/11/2021 2:1219/11/2021 2:1219/11/2021 5:505
19/11/2021 5:5019/11/2021 5:5019/11/2021 13:205
19/11/2021 13:2019/11/2021 13:2019/11/2021 19:175
19/11/2021 19:1719/11/2021 19:1820/11/2021 16:065
20/11/2021 16:0620/11/2021 18:0420/11/2021 18:436
20/11/2021 18:4320/11/2021 19:3521/11/2021 1:437
21/11/2021 1:4321/11/2021 2:0821/11/2021 14:088
21/11/2021 14:0821/11/2021 14:0821/11/2021 16:148
21/11/2021 16:1421/11/2021 16:4121/11/2021 16:469
21/11/2021 16:4621/11/2021 17:3622/11/2021 0:3910
22/11/2021 0:3922/11/2021 1:0022/11/2021 9:1511
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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
Not applicable

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?

 

gjunhao1_0-1638252218467.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.