Summarized table from Detailed one based on complex duration rule

[Mann]

Hi Guys,

Need your help to find a solution to this. I have detailed table showing Ins and Outs for some IDs as shown:

ID	Name	Ins Time	Outs Time	Index	GapDurationSec	GapDurationHours
1	AAA	8/1/2019 8:00	8/1/2019 18:00	0	0	00:00
1	AAA	8/2/2019 10:00	8/3/2019 11:30	1	57600	16:00
1	AAA	8/3/2019 22:00	8/4/2019 5:00	2	37800	10:30
1	AAA	8/4/2019 8:00	8/4/2019 18:00	3	10800	03:00
1	AAA	8/6/2019 8:00	8/6/2019 19:00	4	136800	38:00
1	BBB	8/1/2019 19:30	8/1/2019 23:30	5	0	00:00
1	BBB	8/2/2019 2:00	8/2/2019 18:00	6	9000	02:30
1	BBB	8/3/2019 8:40	8/3/2019 10:00	7	52800	14:40
1	BBB	8/4/2019 6:00	8/5/2019 2:00	8	72000	20:00
1	BBB	8/5/2019 18:00	8/5/2019 22:00	9	57600	16:00
1	DDD	8/1/2019 6:00	8/1/2019 10:00	10	0	00:00
1	DDD	8/1/2019 10:30	8/1/2019 13:00	11	1800	00:30
1	DDD	8/1/2019 13:30	8/1/2019 20:00	12	1800	00:30
1	DDD	8/2/2019 13:00	8/2/2019 20:00	13	61200	17:00
1	DDD	8/2/2019 21:10	8/3/2019 1:00	14	4200	01:10
1	DDD	8/3/2019 2:10	8/3/2019 15:00	15	4200	01:10
1	DDD	8/3/2019 15:30	8/3/2019 17:30	16	1800	00:30
1	DDD	8/4/2019 0:00	8/4/2019 4:00	17	23400	06:30
1	DDD	8/4/2019 4:55	8/4/2019 11:30	18	3300	00:55
1	DDD	8/5/2019 8:00	8/5/2019 9:00	19	73800	20:30
1	DDD	8/5/2019 9:15	8/6/2019 10:00	20	900	00:15
1	DDD	8/6/2019 14:00	8/6/2019 16:00	21	14400	04:00
1	DDD	8/6/2019 17:00	8/6/2019 18:40	22	3600	01:00

I added Index column using power query and found the Gap Duration also. Logic for Gap Duration is the difference in Ins Time of Current row and Outs Time of Previous Row. Following is the DAX Code for Gap Duration Sec:

VAR GapDuration =
    DATEDIFF (
        LOOKUPVALUE (
            'Test'[Out Time],
            'Test'[Index], 'Test'[Index] - 1,
            'Test'[ID], 'Test'[ID]
        ),
        'Test'[In Time ],
        SECOND
    )
VAR InSec =
    IF ( GapDuration = BLANK (), 0, GapDuration )
Return
InSec

My final aim is to find a summarized DAX Calculated table based on below rule:

1) If Gap Duration Hours is <= 5hours (05:00) but not 0 hours then I want to combine the multiple rows to one row.

2) This calculation should run for each Id separately.

For example:

a) Index column value =3 should get merged with previous row since the Gap Duration is 03:00 hrs and create one row.

b) Index row 19,20,21,22 should get merged to get one row based on same rule as the duration is less 

This is the outcome I want to achieve:

ID	Name	Ins Time	Outs Time
1	AAA	8/1/2019 8:00	8/1/2019 18:00
1	AAA	8/2/2019 10:00	8/3/2019 11:30
1	AAA	8/3/2019 22:00	8/4/2019 18:00
1	AAA	8/6/2019 8:00	8/6/2019 19:00
1	BBB	8/1/2019 19:30	8/2/2019 18:00
1	BBB	8/3/2019 8:40	8/3/2019 10:00
1	BBB	8/4/2019 6:00	8/5/2019 2:00
1	BBB	8/5/2019 18:00	8/5/2019 22:00
1	DDD	8/1/2019 6:00	8/1/2019 20:00
1	DDD	8/2/2019 13:00	8/3/2019 17:30
1	DDD	8/4/2019 0:00	8/4/2019 11:30
1	DDD	8/5/2019 8:00	8/6/2019 18:40

Thanks.

[AlB · Accepted solution]

Hi @Mann 

DAX doesn't lend itself well for this. Not a very elegant solution, but try this:

1. Create a new calculated column in your initial table (Table1):

ToMerge =
VAR NextDuration_ =
    CALCULATE (
        DISTINCT ( Table1[GapDurationSec] );
        Table1[Index]
            = ( EARLIER ( Table1[Index] ) + 1 );
        ALLEXCEPT ( Table1; Table1[Name]; Table1[ID] )
    )
RETURN
    IF (
        Table1[GapDurationSec] <= ( 5 * 3600 ) && Table1[GapDurationSec] <> 0;
        IF ( NextDuration_ <= ( 5 * 3600 ) && NextDuration_ <> 0; "M"; "MF" )
    )

2. Create another calculated column:

NewOutsTime =
VAR NextToMerge_ =
    CALCULATE (
        DISTINCT ( Table1[ToMerge] );
        Table1[Index]
            = ( EARLIER ( Table1[Index] ) + 1 );
        ALLEXCEPT ( Table1; Table1[Name]; Table1[ID] )
    )
RETURN
    IF (
        NextToMerge_ IN { "M"; "MF" }
            && ISBLANK ( Table1[ToMerge] );
        VAR NextMF_ =
            CALCULATE (
                MIN ( Table1[Index] );
                Table1[Index] > EARLIER ( Table1[Index] );
                Table1[ToMerge] = "MF";
                ALLEXCEPT ( Table1; Table1[Name]; Table1[ID] )
            )
        RETURN
            CALCULATE (
                DISTINCT ( Table1[Outs Time] );
                Table1[Index] = NextMF_;
                ALLEXCEPT ( Table1; Table1[Name]; Table1[ID] )
            );
        Table1[OutsTime]
    )

Now to get to the final result, you want to ONLY keep rows that are blank on [ToMerge]. [NewOutsTime] will have the correct (merged) [OutsTime].

You can then create another table (cleaner) from that, with SELECTCOLUMNS, for instance. I do not have time for that now.  Note I'm not using the column [GapDurationHours] at all.

Cheers

Code formatted with  

[Mann]

Hi @AlB 

This is some crazy good DAX coding. Thanks alot it works as expected!

Mann.