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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
lorolive
Regular Visitor

DAX: Generating dynamic rows according to condition

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:

StartDateStartTimeEndDateEndTimeId
Donnerstag, 13. April 202310:30:06Donnerstag, 13. April 202310:54:461161
Donnerstag, 13. April 202312:30:00Donnerstag, 13. April 202316:36:001162
Freitag, 5. Mai 202323:00:03Samstag, 6. Mai 202300:50:201186
Dienstag, 9. Mai 202323:00:06Mittwoch, 10. Mai 202300:50:281187
Montag, 8. Mai 202323:00:00Dienstag, 9. Mai 202300:51:031188


I have also created the 'HourlyIntervals' table

 

Value  HourlyIntervalStartHourlyIntervalEndIntervalDuration
000:00:0001:00:0001:00:00
101:00:0002:00:0001:00:00
202:00:0003:00:0001:00:00
303:00:0004:00:0001:00:00
404:00:0005:00:0001:00:00
505:00:0006:00:0001:00:00
606:00:0007:00:0001:00:00
707:00:0008:00:0001:00:00
808:00:0009:00:0001:00:00
909:00:0010:00:0001:00:00
1010:00:0011:00:0001:00:00
1111:00:0012:00:0001:00:00
1212:00:0013:00:0001:00:00
1313:00:0014:00:0001:00:00
1414:00:0015:00:0001:00:00
1515:00:0016:00:0001:00:00
1616:00:0017:00:0001:00:00
1717:00:0018:00:0001:00:00
1818:00:0019:00:0001:00:00
1919:00:0020:00:0001:00:00
2020:00:0021:00:0001:00:00
2121:00:0022:00:0001:00:00
2222:00:0023:00:0001:00:00
2323:00:0000:00:0001: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:

 

StartDateStartTimeEndDateEndTimeId
Donnerstag, 13. April 202310:30:06Donnerstag, 13. April 202310:54:461161

 

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:

 

StartDateStartTimeEndDateEndTimeId
Donnerstag, 13. April 202312:30:05Donnerstag, 13. April 202316:36:531162


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

 

StartDateStartTimeEndDateEndTimeIdHourlyIntervalStartHourlyIntervalEndDuration
Donnerstag, 13. April 202312:30:00Donnerstag, 13. April 202316:36:00116212:00:0013:00:0000:30:00
Donnerstag, 13. April 202312:30:00Donnerstag, 13. April 202316:36:00116213:00:0014:00:0001:00:00
Donnerstag, 13. April 202312:30:00Donnerstag, 13. April 202316:36:00116214:00:0015:00:0001:00:00
Donnerstag, 13. April 202312:30:00Donnerstag, 13. April 202316:36:00116215:00:0016:00:0001:00:00
Donnerstag, 13. April 202312:30:00Donnerstag, 13. April 202316:36:00116216:00:0017:00:0000:24:00


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

 

StartDateStartTimeEndDateEndTimeId
Montag, 8. Mai 202323:00:00Dienstag, 9. Mai 202300:51:031188


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:

 

StartDateStartTimeEndDateEndTimeIdHourlyIntervalStartHourlyIntervalEndDuration
Montag, 8. Mai 202323:00:00Dienstag, 9. Mai 202300:51:03118823:00:0000:00:0001:00:00
Montag, 8. Mai 202323:00:00Dienstag, 9. Mai 202300:51:03118800:00:0001:00:0000:08:57


I would appreciate your suggestions. Thanks!

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @lorolive , 

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

veqinmsft_0-1685004478879.png

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

veqinmsft_1-1685004478881.png

3. The same operation yields the End Date.

veqinmsft_2-1685004555440.png

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 

veqinmsft_3-1685004598938.png

veqinmsft_4-1685004598939.png

 5. Create table 

veqinmsft_5-1685004667759.png

6. Merge table. 

Home – Merge Queries -- Merge Queries as New 

veqinmsft_6-1685004689402.png

 

veqinmsft_7-1685004689402.png

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

veqinmsft_8-1685004720888.png

Result: 

veqinmsft_9-1685004720889.png8. 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]) 

veqinmsft_10-1685004766803.png

 

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 

View solution in original post

2 REPLIES 2
lorolive
Regular Visitor

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

v-eqin-msft
Community Support
Community Support

Hi @lorolive , 

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

veqinmsft_0-1685004478879.png

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

veqinmsft_1-1685004478881.png

3. The same operation yields the End Date.

veqinmsft_2-1685004555440.png

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 

veqinmsft_3-1685004598938.png

veqinmsft_4-1685004598939.png

 5. Create table 

veqinmsft_5-1685004667759.png

6. Merge table. 

Home – Merge Queries -- Merge Queries as New 

veqinmsft_6-1685004689402.png

 

veqinmsft_7-1685004689402.png

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

veqinmsft_8-1685004720888.png

Result: 

veqinmsft_9-1685004720889.png8. 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]) 

veqinmsft_10-1685004766803.png

 

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 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors