cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mann
Resolver III
Resolver III

Summarized table from Detailed one based on complex duration rule

Hi Guys,

 

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

 

IDNameIns TimeOuts TimeIndexGapDurationSecGapDurationHours
1AAA8/1/2019 8:008/1/2019 18:000000:00
1AAA8/2/2019 10:008/3/2019 11:3015760016:00
1AAA8/3/2019 22:008/4/2019 5:0023780010:30
1AAA8/4/2019 8:008/4/2019 18:0031080003:00
1AAA8/6/2019 8:008/6/2019 19:00413680038:00
1BBB8/1/2019 19:308/1/2019 23:305000:00
1BBB8/2/2019 2:008/2/2019 18:006900002:30
1BBB8/3/2019 8:408/3/2019 10:0075280014:40
1BBB8/4/2019 6:008/5/2019 2:0087200020:00
1BBB8/5/2019 18:008/5/2019 22:0095760016:00
1DDD8/1/2019 6:008/1/2019 10:0010000:00
1DDD8/1/2019 10:308/1/2019 13:0011180000:30
1DDD8/1/2019 13:308/1/2019 20:0012180000:30
1DDD8/2/2019 13:008/2/2019 20:00136120017:00
1DDD8/2/2019 21:108/3/2019 1:0014420001:10
1DDD8/3/2019 2:108/3/2019 15:0015420001:10
1DDD8/3/2019 15:308/3/2019 17:3016180000:30
1DDD8/4/2019 0:008/4/2019 4:00172340006:30
1DDD8/4/2019 4:558/4/2019 11:3018330000:55
1DDD8/5/2019 8:008/5/2019 9:00197380020:30
1DDD8/5/2019 9:158/6/2019 10:002090000:15
1DDD8/6/2019 14:008/6/2019 16:00211440004:00
1DDD8/6/2019 17:008/6/2019 18:4022360001: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:

 

IDNameIns TimeOuts Time
1AAA8/1/2019 8:008/1/2019 18:00
1AAA8/2/2019 10:008/3/2019 11:30
1AAA8/3/2019 22:008/4/2019 18:00
1AAA8/6/2019 8:008/6/2019 19:00
1BBB8/1/2019 19:308/2/2019 18:00
1BBB8/3/2019 8:408/3/2019 10:00
1BBB8/4/2019 6:008/5/2019 2:00
1BBB8/5/2019 18:008/5/2019 22:00
1DDD8/1/2019 6:008/1/2019 20:00
1DDD8/2/2019 13:008/3/2019 17:30
1DDD8/4/2019 0:008/4/2019 11:30
1DDD8/5/2019 8:008/6/2019 18:40

 

Thanks.

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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   www.daxformatter.com

View solution in original post

2 REPLIES 2
AlB
Super User
Super User

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   www.daxformatter.com

View solution in original post

Mann
Resolver III
Resolver III

Hi @AlB 

 

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

 

Mann.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.