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

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

Accepted Solutions
Highlighted
Super User
Super User

Re: Summarized table from Detailed one based on complex duration rule

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
Highlighted
Super User
Super User

Re: Summarized table from Detailed one based on complex duration rule

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 Member
Member

Re: Summarized table from Detailed one based on complex duration rule

Hi @AlB 

 

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

 

Mann.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)