DAX: Generating dynamic rows according to condition

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