DAX: Generating dynamic rows according to condition

Question

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:StartDateStartTimeEndDateEndTimeIdDonnerstag, 13. April 202310:30:06Donnerstag, 13. April 202310:54:461161Donnerstag, 13. April 202312:30:00Donnerstag, 13. April 202316:36:001162Freitag, 5. Mai 202323:00:03Samstag, 6. Mai 202300:50:201186Dienstag, 9. Mai 202323:00:06Mittwoch, 10. Mai 202300:50:281187Montag, 8. Mai 202323:00:00Dienstag, 9. Mai 202300:51:031188I have also created the 'HourlyIntervals' table&nbsp;Value&nbsp;&nbsp;HourlyIntervalStartHourlyIntervalEndIntervalDuration000:00:0001:00:0001:00:00101:00:0002:00:0001:00:00202:00:0003:00:0001:00:00303:00:0004:00:0001:00:00404:00:0005:00:0001:00:00505:00:0006:00:0001:00:00606:00:0007:00:0001:00:00707:00:0008:00:0001:00:00808:00:0009:00:0001:00:00909:00:0010:00:0001:00:001010:00:0011:00:0001:00:001111:00:0012:00:0001:00:001212:00:0013:00:0001:00:001313:00:0014:00:0001:00:001414:00:0015:00:0001:00:001515:00:0016:00:0001:00:001616:00:0017:00:0001:00:001717:00:0018:00:0001:00:001818:00:0019:00:0001:00:001919:00:0020:00:0001:00:002020:00:0021:00:0001:00:002121:00:0022:00:0001:00:002222:00:0023:00:0001:00:002323:00:0000:00:0001:00:00What 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:&nbsp;StartDateStartTimeEndDateEndTimeIdDonnerstag, 13. April 202310:30:06Donnerstag, 13. April 202310:54:461161&nbsp;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:&nbsp;StartDateStartTimeEndDateEndTimeIdDonnerstag, 13. April 202312:30:05Donnerstag, 13. April 202316:36:531162I have StartDate = EndDate and the activity belongs to 5 intervals, so I expect the results below (special attention to the duration):&nbsp;StartDateStartTimeEndDateEndTimeIdHourlyIntervalStartHourlyIntervalEndDurationDonnerstag, 13. April 202312:30:00Donnerstag, 13. April 202316:36:00116212:00:0013:00:0000:30:00Donnerstag, 13. April 202312:30:00Donnerstag, 13. April 202316:36:00116213:00:0014:00:0001:00:00Donnerstag, 13. April 202312:30:00Donnerstag, 13. April 202316:36:00116214:00:0015:00:0001:00:00Donnerstag, 13. April 202312:30:00Donnerstag, 13. April 202316:36:00116215:00:0016:00:0001:00:00Donnerstag, 13. April 202312:30:00Donnerstag, 13. April 202316:36:00116216:00:0017:00:0000:24:003. The third scenario is when StartDate &lt;&gt; EndDate, like the Id below:&nbsp;StartDateStartTimeEndDateEndTimeIdMontag, 8. Mai 202323:00:00Dienstag, 9. Mai 202300:51:031188Expected 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:&nbsp;StartDateStartTimeEndDateEndTimeIdHourlyIntervalStartHourlyIntervalEndDurationMontag, 8. Mai 202323:00:00Dienstag, 9. Mai 202300:51:03118823:00:0000:00:0001:00:00Montag, 8. Mai 202323:00:00Dienstag, 9. Mai 202300:51:03118800:00:0001:00:0000:08:57I would appreciate your suggestions. Thanks!

anonymous · Accepted Answer

Hi Anonymous&nbsp;,&nbsp;
Here are the steps you can follow：&nbsp;
1. Use Custom Columns in Power Query to stitch together Date and Time.&nbsp;
Date.ToText([StartDate])&amp;" "&amp;Time.ToText([StartTime])&nbsp;

2. [Customer] -- Transform – Date/Time&nbsp;

3. The same operation yields the End Date.

4. Add Column – Custom Column -- Create three columns to get the hour, minute, and index&nbsp;
(Duration.Days([EndTimeALL] - [StartTimeALL]) *24 ) + (Duration.Hours([EndTimeALL] - [StartTimeALL])) (Duration.Minutes([EndTimeALL]- [StartTimeALL])) (Duration.Days([EndTimeALL] - [StartTimeALL]) *24 ) + (Duration.Hours([EndTimeALL] - [StartTimeALL])) 
+1

&nbsp;5.&nbsp;Create table&nbsp;

6. Merge table.&nbsp;
Home – Merge Queries -- Merge Queries as New&nbsp;

&nbsp;

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

Result:&nbsp;
8.&nbsp;Create calculated column:&nbsp;
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])&amp;&amp;'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 &amp;&amp; _flag&gt;0, 
    TIME(0,_flag,0), 
IF( 
    'Merge1'[True_Table.Index]=_max, 
     TIME( 
        0,[Minute],0), 
    TIME(1,0,0)))) 
9. Create calculated table:&nbsp;
Table =  
SUMMARIZE( 
    'Merge1',     'Merge1'[StartDate],'Merge1'[StartTime],'Merge1'[EndDate],'Merge1'[EndTime],'Merge1'[Id],'Merge1'[Duration])

&nbsp;
Best Regards,&nbsp;
Liu Yang&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly&nbsp;

Forum Discussion

DAX: Generating dynamic rows according to condition

2 Replies

Related content

Generating Missing Data

Combine according to condition

Dynamically generate calculate filter conditions

Generating link

Rank with conditions

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

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