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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.