topic Re: grouping with dax or power query in DAX Commands and Tips

grouping with dax or power query

Anonymous — Tue, 11 May 2021 13:07:56 GMT

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.

Re: grouping with dax or power query

Anonymous — Tue, 11 May 2021 13:36:37 GMT

amitchandak

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

Re: grouping with dax or power query

amitchandak — Tue, 11 May 2021 13:37:57 GMT

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

Re: grouping with dax or power query

Anonymous — Tue, 11 May 2021 14:21:49 GMT

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.

Re: grouping with dax or power query

amitchandak — Tue, 11 May 2021 14:46:12 GMT

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

Re: grouping with dax or power query

Anonymous — Tue, 11 May 2021 15:44:04 GMT

Thank you so much amitchandak it is working perfectly