grouping with dax or power query

[Anonymous]

Hi, Team please help with the below task.

StartTime	EndTime	Required column
01/07/2020 12:00:05 AM	01/07/2020 12:00:07 AM	0
01/07/2020 12:00:06 AM	01/07/2020 12:00:07 AM	0
01/07/2020 12:00:07 AM	01/07/2020 12:00:07 AM	0
01/07/2020 12:05:18 AM	01/07/2020 12:06:52 AM	1
01/07/2020 12:06:42 AM	01/07/2020 12:06:42 AM	1
01/07/2020 12:06:42 AM	01/07/2020 12:06:42 AM	1
01/07/2020 12:15:54 AM	01/07/2020 12:15:56 AM	2
01/07/2020 12:16:03 AM	01/07/2020 12:16:06 AM	3
01/07/2020 12:16:27 AM	01/07/2020 12:16:39 AM	4
01/07/2020 12:16:39 AM	01/07/2020 12:18:30 AM	4
01/07/2020 12:18:30 AM	01/07/2020 12:19:52 AM	5
01/07/2020 12:20:41 AM	01/07/2020 12:22:27 AM	6
01/07/2020 12:20:42 AM	01/07/2020 12:22:27 AM	6
01/07/2020 12:20:42 AM	01/07/2020 12:22:27 AM	6
01/07/2020 12:22:27 AM	01/07/2020 12:23:25 AM	6
01/07/2020 12:23:00 AM	01/07/2020 12:23:15 AM	7
01/07/2020 12:23:00 AM	01/07/2020 12:28:01 AM	7
01/07/2020 12:23:42 AM	01/07/2020 12:28:03 AM	8

If we take the 1st Endtime of the column it should be less than or equal to each row of the Starttime then it will make it as one group and grouping should start with 0, 1, 2, 3....

 @amitchandak,

Thank you in advance.

[Anonymous]

@amitchandak 

Please find the above image highlighted colors are grouping rule for better understanding, I am expecting the result is "Required Column"

[amitchandak]

@Anonymous , I tried but I am getting a few diffs. can you check the first column I created is correct as per your conditions?

New Time = 
var _ed =[EndTime]  
var _2 = maxx(filter('Table',[StartTime] <=_ed),[StartTime]) 
return _2

Then rank 

Rank = RANKX('Table',[New Time],,ASC,Dense) -1

I doubt I have not taken new time correctly here

[Anonymous]

Hi Amit,

Thank you for your great support and everything perfect but a few rows are unmatched please have look at below those are highlighted in red color font.

[amitchandak]

@Anonymous , Please change new time column as give below and try

New Time = 
var _ed =[EndTime]  
var _st =[StartTime]  
var _led = maxx(filter('Table', [StartTime] <EARLIER([StartTime])),[EndTime])
var _2 = if(ISBLANK(_led) ||  [StartTime] > _led ,_ed,_led)

return _2

[Anonymous · Accepted solution]

Thank you so much @amitchandak  it is working perfectly