DAX: Generating dynamic rows according to condition

[Anonymous]

Hello community members,

I am trying to achieve a somehow complicated task on Power BI.

I have a table monitoring activities. A sample can be found below:

StartDate	StartTime	EndDate	EndTime	Id
Donnerstag, 13. April 2023	10:30:06	Donnerstag, 13. April 2023	10:54:46	1161
Donnerstag, 13. April 2023	12:30:00	Donnerstag, 13. April 2023	16:36:00	1162
Freitag, 5. Mai 2023	23:00:03	Samstag, 6. Mai 2023	00:50:20	1186
Dienstag, 9. Mai 2023	23:00:06	Mittwoch, 10. Mai 2023	00:50:28	1187
Montag, 8. Mai 2023	23:00:00	Dienstag, 9. Mai 2023	00:51:03	1188

I have also created the 'HourlyIntervals' table

Value	HourlyIntervalStart	HourlyIntervalEnd	IntervalDuration
0	00:00:00	01:00:00	01:00:00
1	01:00:00	02:00:00	01:00:00
2	02:00:00	03:00:00	01:00:00
3	03:00:00	04:00:00	01:00:00
4	04:00:00	05:00:00	01:00:00
5	05:00:00	06:00:00	01:00:00
6	06:00:00	07:00:00	01:00:00
7	07:00:00	08:00:00	01:00:00
8	08:00:00	09:00:00	01:00:00
9	09:00:00	10:00:00	01:00:00
10	10:00:00	11:00:00	01:00:00
11	11:00:00	12:00:00	01:00:00
12	12:00:00	13:00:00	01:00:00
13	13:00:00	14:00:00	01:00:00
14	14:00:00	15:00:00	01:00:00
15	15:00:00	16:00:00	01:00:00
16	16:00:00	17:00:00	01:00:00
17	17:00:00	18:00:00	01:00:00
18	18:00:00	19:00:00	01:00:00
19	19:00:00	20:00:00	01:00:00
20	20:00:00	21:00:00	01:00:00
21	21:00:00	22:00:00	01:00:00
22	22:00:00	23:00:00	01:00:00
23	23:00:00	00:00:00	01:00:00

What I want to achieve is:

I want to generate, for each activity of my first table, dynamic rows according to the activity duration. For each row, the duration within the hour should be calculated. I will give 3 possible situations with the expected results:

1. For Id = 1161 below:

StartDate	StartTime	EndDate	EndTime	Id
Donnerstag, 13. April 2023	10:30:06	Donnerstag, 13. April 2023	10:54:46	1161

Expected results: I have StartDate = EndDate, the activity belong to the hourly interval from 10:00:00 to 11:00:00, so one row should be generated with HourlyIntervalStart = 10:00:00, HourlyIntervalEnd = 11:00:00, Duration = EndTime - StartTime = 00:24:40.

2. For Id = 1162 below:

StartDate	StartTime	EndDate	EndTime	Id
Donnerstag, 13. April 2023	12:30:05	Donnerstag, 13. April 2023	16:36:53	1162

I have StartDate = EndDate and the activity belongs to 5 intervals, so I expect the results below (special attention to the duration):

StartDate	StartTime	EndDate	EndTime	Id	HourlyIntervalStart	HourlyIntervalEnd	Duration
Donnerstag, 13. April 2023	12:30:00	Donnerstag, 13. April 2023	16:36:00	1162	12:00:00	13:00:00	00:30:00
Donnerstag, 13. April 2023	12:30:00	Donnerstag, 13. April 2023	16:36:00	1162	13:00:00	14:00:00	01:00:00
Donnerstag, 13. April 2023	12:30:00	Donnerstag, 13. April 2023	16:36:00	1162	14:00:00	15:00:00	01:00:00
Donnerstag, 13. April 2023	12:30:00	Donnerstag, 13. April 2023	16:36:00	1162	15:00:00	16:00:00	01:00:00
Donnerstag, 13. April 2023	12:30:00	Donnerstag, 13. April 2023	16:36:00	1162	16:00:00	17:00:00	00:24:00

3. The third scenario is when StartDate <> EndDate, like the Id below:

StartDate	StartTime	EndDate	EndTime	Id
Montag, 8. Mai 2023	23:00:00	Dienstag, 9. Mai 2023	00:51:03	1188

Expected result: This is similar to scenario 2, but as the day is different, the calculation needs to consider it in order to result in the following 2 rows:

StartDate	StartTime	EndDate	EndTime	Id	HourlyIntervalStart	HourlyIntervalEnd	Duration
Montag, 8. Mai 2023	23:00:00	Dienstag, 9. Mai 2023	00:51:03	1188	23:00:00	00:00:00	01:00:00
Montag, 8. Mai 2023	23:00:00	Dienstag, 9. Mai 2023	00:51:03	1188	00:00:00	01:00:00	00:08:57

I would appreciate your suggestions. Thanks!

[Anonymous]

@v-eqin-msft thanks a lot, with slight modifications for my data it worked perfectly!

[v-eqin-msft · Accepted solution]

Hi @Anonymous , 

Here are the steps you can follow： 

1. Use Custom Columns in Power Query to stitch together Date and Time. 

Date.ToText([StartDate])&" "&Time.ToText([StartTime]) 

2. [Customer] -- Transform – Date/Time 

3. The same operation yields the End Date.

4. Add Column – Custom Column -- Create three columns to get the hour, minute, and index 

(Duration.Days([EndTimeALL] - [StartTimeALL]) *24 ) + (Duration.Hours([EndTimeALL] - [StartTimeALL])) 

(Duration.Minutes([EndTimeALL]- [StartTimeALL])) 

(Duration.Days([EndTimeALL] - [StartTimeALL]) *24 ) + (Duration.Hours([EndTimeALL] - [StartTimeALL])) 
+1 

 5. Create table 

6. Merge table. 

Home – Merge Queries -- Merge Queries as New 

7. Click on the extension icon and only this [lndex] -- OK 

Result: 

8. Create calculated column: 

Duration =  
var _max= 
MAXX( 
    FILTER(ALL(Merge1), 
    'Merge1'[Custom]=EARLIER('Merge1'[Custom])),[True_Table.Index]) 
var _min= 
MINX( 
    FILTER(ALL(Merge1), 
    'Merge1'[Custom]=EARLIER('Merge1'[Custom])),[True_Table.Index]) 
var _value= 
MAXX(   FILTER(ALL(Merge1),'Merge1'[Custom]=EARLIER('Merge1'[Custom])&&'Merge1'[True_Table.Index]=_min),[StartTime]) 
var _flag= 
DATEDIFF( 
_value, 
TIME( 
    HOUR(_value)+1,0,0),MINUTE) 
var _count= 
COUNTX( 
 FILTER(ALL(Merge1), 
    'Merge1'[Custom]=EARLIER('Merge1'[Custom])),[True_Table.Index]) 
return 
IF( 
    _count = 1, 
    TIME( 
        0,[Minute],0), 
IF( 
    'Merge1'[True_Table.Index]=_min && _flag>0, 
    TIME(0,_flag,0), 
IF( 
    'Merge1'[True_Table.Index]=_max, 
     TIME( 
        0,[Minute],0), 
    TIME(1,0,0)))) 

9. Create calculated table: 

Table =  
SUMMARIZE( 
    'Merge1',     'Merge1'[StartDate],'Merge1'[StartTime],'Merge1'[EndDate],'Merge1'[EndTime],'Merge1'[Id],'Merge1'[Duration]) 

Best Regards, 

Liu Yang 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly