topic Re: Summarized table from Detailed one based on complex duration rule in Desktop
https://community.powerbi.com/t5/Desktop/Summarized-table-from-Detailed-one-based-on-complex-duration/m-p/771215#M371576
<P>Hi <LI-USER uid="92178"></LI-USER> </P><P> </P><P>This is some crazy good DAX coding. Thanks alot it works as expected!</P><P> </P><P>Mann.</P>Tue, 20 Aug 2019 20:18:13 GMTMann2019-08-20T20:18:13ZSummarized table from Detailed one based on complex duration rule
https://community.powerbi.com/t5/Desktop/Summarized-table-from-Detailed-one-based-on-complex-duration/m-p/770217#M371148
<P>Hi Guys,</P><P> </P><P>Need your help to find a solution to this. I have detailed table showing Ins and Outs for some IDs as shown:</P><P> </P><TABLE><TBODY><TR><TD><STRONG>ID</STRONG></TD><TD><STRONG>Name</STRONG></TD><TD><STRONG>Ins Time</STRONG></TD><TD><STRONG>Outs Time</STRONG></TD><TD><STRONG>Index</STRONG></TD><TD><STRONG>GapDurationSec</STRONG></TD><TD><STRONG>GapDurationHours</STRONG></TD></TR><TR><TD>1</TD><TD>AAA</TD><TD>8/1/2019 8:00</TD><TD>8/1/2019 18:00</TD><TD>0</TD><TD>0</TD><TD>00:00</TD></TR><TR><TD>1</TD><TD>AAA</TD><TD>8/2/2019 10:00</TD><TD>8/3/2019 11:30</TD><TD>1</TD><TD>57600</TD><TD>16:00</TD></TR><TR><TD>1</TD><TD>AAA</TD><TD>8/3/2019 22:00</TD><TD>8/4/2019 5:00</TD><TD>2</TD><TD>37800</TD><TD>10:30</TD></TR><TR><TD>1</TD><TD>AAA</TD><TD>8/4/2019 8:00</TD><TD>8/4/2019 18:00</TD><TD>3</TD><TD>10800</TD><TD>03:00</TD></TR><TR><TD>1</TD><TD>AAA</TD><TD>8/6/2019 8:00</TD><TD>8/6/2019 19:00</TD><TD>4</TD><TD>136800</TD><TD>38:00</TD></TR><TR><TD>1</TD><TD>BBB</TD><TD>8/1/2019 19:30</TD><TD>8/1/2019 23:30</TD><TD>5</TD><TD>0</TD><TD>00:00</TD></TR><TR><TD>1</TD><TD>BBB</TD><TD>8/2/2019 2:00</TD><TD>8/2/2019 18:00</TD><TD>6</TD><TD>9000</TD><TD>02:30</TD></TR><TR><TD>1</TD><TD>BBB</TD><TD>8/3/2019 8:40</TD><TD>8/3/2019 10:00</TD><TD>7</TD><TD>52800</TD><TD>14:40</TD></TR><TR><TD>1</TD><TD>BBB</TD><TD>8/4/2019 6:00</TD><TD>8/5/2019 2:00</TD><TD>8</TD><TD>72000</TD><TD>20:00</TD></TR><TR><TD>1</TD><TD>BBB</TD><TD>8/5/2019 18:00</TD><TD>8/5/2019 22:00</TD><TD>9</TD><TD>57600</TD><TD>16:00</TD></TR><TR><TD>1</TD><TD>DDD</TD><TD>8/1/2019 6:00</TD><TD>8/1/2019 10:00</TD><TD>10</TD><TD>0</TD><TD>00:00</TD></TR><TR><TD>1</TD><TD>DDD</TD><TD>8/1/2019 10:30</TD><TD>8/1/2019 13:00</TD><TD>11</TD><TD>1800</TD><TD>00:30</TD></TR><TR><TD>1</TD><TD>DDD</TD><TD>8/1/2019 13:30</TD><TD>8/1/2019 20:00</TD><TD>12</TD><TD>1800</TD><TD>00:30</TD></TR><TR><TD>1</TD><TD>DDD</TD><TD>8/2/2019 13:00</TD><TD>8/2/2019 20:00</TD><TD>13</TD><TD>61200</TD><TD>17:00</TD></TR><TR><TD>1</TD><TD>DDD</TD><TD>8/2/2019 21:10</TD><TD>8/3/2019 1:00</TD><TD>14</TD><TD>4200</TD><TD>01:10</TD></TR><TR><TD>1</TD><TD>DDD</TD><TD>8/3/2019 2:10</TD><TD>8/3/2019 15:00</TD><TD>15</TD><TD>4200</TD><TD>01:10</TD></TR><TR><TD>1</TD><TD>DDD</TD><TD>8/3/2019 15:30</TD><TD>8/3/2019 17:30</TD><TD>16</TD><TD>1800</TD><TD>00:30</TD></TR><TR><TD>1</TD><TD>DDD</TD><TD>8/4/2019 0:00</TD><TD>8/4/2019 4:00</TD><TD>17</TD><TD>23400</TD><TD>06:30</TD></TR><TR><TD>1</TD><TD>DDD</TD><TD>8/4/2019 4:55</TD><TD>8/4/2019 11:30</TD><TD>18</TD><TD>3300</TD><TD>00:55</TD></TR><TR><TD>1</TD><TD>DDD</TD><TD>8/5/2019 8:00</TD><TD>8/5/2019 9:00</TD><TD>19</TD><TD>73800</TD><TD>20:30</TD></TR><TR><TD>1</TD><TD>DDD</TD><TD>8/5/2019 9:15</TD><TD>8/6/2019 10:00</TD><TD>20</TD><TD>900</TD><TD>00:15</TD></TR><TR><TD>1</TD><TD>DDD</TD><TD>8/6/2019 14:00</TD><TD>8/6/2019 16:00</TD><TD>21</TD><TD>14400</TD><TD>04:00</TD></TR><TR><TD>1</TD><TD>DDD</TD><TD>8/6/2019 17:00</TD><TD>8/6/2019 18:40</TD><TD>22</TD><TD>3600</TD><TD>01:00</TD></TR></TBODY></TABLE><P> </P><P>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:</P><P> </P><PRE>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</PRE><P><STRONG>My final aim is to find a summarized DAX Calculated table based on below rule:</STRONG></P><P> </P><P>1) If Gap Duration Hours is <= 5hours (05:00) but not 0 hours then I want to combine the multiple rows to one row.</P><P>2) This calculation should run for each Id separately.</P><P>For example:</P><P>a) Index column value =3 should get merged with previous row since the Gap Duration is 03:00 hrs and create one row.</P><P>b) Index row 19,20,21,22 should get merged to get one row based on same rule as the duration is less </P><P> </P><P>This is the outcome I want to achieve:</P><P> </P><TABLE><TBODY><TR><TD><STRONG>ID</STRONG></TD><TD><STRONG>Name</STRONG></TD><TD><STRONG>Ins Time</STRONG></TD><TD><STRONG>Outs Time</STRONG></TD></TR><TR><TD>1</TD><TD>AAA</TD><TD>8/1/2019 8:00</TD><TD>8/1/2019 18:00</TD></TR><TR><TD>1</TD><TD>AAA</TD><TD>8/2/2019 10:00</TD><TD>8/3/2019 11:30</TD></TR><TR><TD>1</TD><TD>AAA</TD><TD>8/3/2019 22:00</TD><TD>8/4/2019 18:00</TD></TR><TR><TD>1</TD><TD>AAA</TD><TD>8/6/2019 8:00</TD><TD>8/6/2019 19:00</TD></TR><TR><TD>1</TD><TD>BBB</TD><TD>8/1/2019 19:30</TD><TD>8/2/2019 18:00</TD></TR><TR><TD>1</TD><TD>BBB</TD><TD>8/3/2019 8:40</TD><TD>8/3/2019 10:00</TD></TR><TR><TD>1</TD><TD>BBB</TD><TD>8/4/2019 6:00</TD><TD>8/5/2019 2:00</TD></TR><TR><TD>1</TD><TD>BBB</TD><TD>8/5/2019 18:00</TD><TD>8/5/2019 22:00</TD></TR><TR><TD>1</TD><TD>DDD</TD><TD>8/1/2019 6:00</TD><TD>8/1/2019 20:00</TD></TR><TR><TD>1</TD><TD>DDD</TD><TD>8/2/2019 13:00</TD><TD>8/3/2019 17:30</TD></TR><TR><TD>1</TD><TD>DDD</TD><TD>8/4/2019 0:00</TD><TD>8/4/2019 11:30</TD></TR><TR><TD>1</TD><TD>DDD</TD><TD>8/5/2019 8:00</TD><TD>8/6/2019 18:40</TD></TR></TBODY></TABLE><P> </P><P>Thanks.</P>Mon, 19 Aug 2019 20:15:12 GMThttps://community.powerbi.com/t5/Desktop/Summarized-table-from-Detailed-one-based-on-complex-duration/m-p/770217#M371148Mann2019-08-19T20:15:12ZRe: Summarized table from Detailed one based on complex duration rule
https://community.powerbi.com/t5/Desktop/Summarized-table-from-Detailed-one-based-on-complex-duration/m-p/770340#M371203
<P>Hi <LI-USER uid="96295"></LI-USER> </P>
<P>DAX doesn't lend itself well for this. Not a very elegant solution, but try this:</P>
<P><STRONG>1</STRONG>. Create a new calculated column in your initial table (Table1):</P>
<PRE>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" )
)
</PRE>
<P><STRONG>2</STRONG>. Create another calculated column:</P>
<PRE>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]
)
</PRE>
<P> </P>
<P>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].</P>
<P>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.</P>
<P>Cheers</P>
<P>Code formatted with <A href="https://www.daxformatter.com" target="_self"><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="www.daxformatter.com" style="width: 100px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/141397iD66F3136F9F75655/image-size/large?v=v2&px=999" role="button" title="www.daxformatter.com" alt="www.daxformatter.com" /></span></A></P>Mon, 19 Aug 2019 23:55:12 GMThttps://community.powerbi.com/t5/Desktop/Summarized-table-from-Detailed-one-based-on-complex-duration/m-p/770340#M371203AlB2019-08-19T23:55:12ZRe: Summarized table from Detailed one based on complex duration rule
https://community.powerbi.com/t5/Desktop/Summarized-table-from-Detailed-one-based-on-complex-duration/m-p/771215#M371576
<P>Hi <LI-USER uid="92178"></LI-USER> </P><P> </P><P>This is some crazy good DAX coding. Thanks alot it works as expected!</P><P> </P><P>Mann.</P>Tue, 20 Aug 2019 20:18:13 GMThttps://community.powerbi.com/t5/Desktop/Summarized-table-from-Detailed-one-based-on-complex-duration/m-p/771215#M371576Mann2019-08-20T20:18:13Z