https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Creating-measure-between-two-table-to-track-hours-in-position/m-p/4407990#M175027 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="928711" data-lia-user-login="Ellen_horton14" class="lia-mention lia-mention-user">Ellen_horton14</a>&nbsp;,</P> <P>&nbsp;</P> <P>I think you can try code as below to create a measure.</P> <LI-CODE lang="markup">Labor hour base on Period = CALCULATE ( SUM ( LaborDetail[Labor Hours] ), FILTER ( LaborDetail, LaborDetail[EMP ID No] = MAX ( ORG[EMP ID No] ) &amp;&amp; AND ( LaborDetail[Pay Period End Date] &gt;= MAX ( ORG[Start] ), LaborDetail[Pay Period End Date] &lt;= MAX ( ORG[End] ) ) ) )</LI-CODE> <P>Result is as below.</P> <P><img /></P> <P>&nbsp;</P> <P>Best Regards,<BR />Rico Zhou</P> <P>&nbsp;</P> <P>If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.</P> Thu, 13 Feb 2025 02:49:19 GMT Anonymous 2025-02-13T02:49:19Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Creating-measure-between-two-table-to-track-hours-in-position/m-p/4407648#M175010 <P>I have 2 tables linked by Emp ID No in a many to many cardinality. I want to be able to look at a specific pay period and see the hours a person worked based on the position, team, division, or branch they were assigned to at the time by comparing the start and End dates in ORG table to the LaborDetail Pay Period dates. I'm very new to anything more complex than point and click in Power BI.</P><P>&nbsp;</P><P>LaborDetail</P><TABLE><TBODY><TR><TD>Direct vs Overhead</TD><TD>EMP ID No</TD><TD>Full Name</TD><TD>Rollup Hrs</TD><TD>Labor Hours</TD><TD>Work Date</TD><TD>Pay Period End Date</TD></TR><TR><TD>Overhead</TD><TD>E1985</TD><TD>Marty McFly</TD><TD>REG</TD><TD>1</TD><TD>1/28/2025</TD><TD>2/8/2025</TD></TR><TR><TD>Direct</TD><TD>E1985</TD><TD>Marty McFly</TD><TD>REG</TD><TD>15</TD><TD>1/29/2025</TD><TD>2/8/2025</TD></TR><TR><TD>Direct</TD><TD>E1985</TD><TD>Marty McFly</TD><TD>REG</TD><TD>31</TD><TD>1/23/2025</TD><TD>1/25/2025</TD></TR><TR><TD>Overhead</TD><TD>E1985</TD><TD>Marty McFly</TD><TD>REG</TD><TD>2</TD><TD>1/23/2025</TD><TD>1/25/2025</TD></TR><TR><TD>Overhead</TD><TD>E1234</TD><TD>MINNIE MOUSE</TD><TD>REG</TD><TD>3</TD><TD>11/6/2024</TD><TD>11/16/2024</TD></TR><TR><TD>Direct</TD><TD>E1234</TD><TD>MINNIE MOUSE</TD><TD>REG</TD><TD>13</TD><TD>11/6/2024</TD><TD>11/16/2024</TD></TR><TR><TD>Direct</TD><TD>E1234</TD><TD>MINNIE MOUSE</TD><TD>REG</TD><TD>7</TD><TD>10/26/2023</TD><TD>11/4/2023</TD></TR><TR><TD>Overhead</TD><TD>E1234</TD><TD>MINNIE MOUSE</TD><TD>REG</TD><TD>1</TD><TD>10/26/2023</TD><TD>11/4/2023</TD></TR><TR><TD>Direct</TD><TD>E5678</TD><TD>Bluey</TD><TD>REG</TD><TD>28</TD><TD>11/14/2023</TD><TD>11/18/2023</TD></TR><TR><TD>Overhead</TD><TD>E5678</TD><TD>Bluey</TD><TD>REG</TD><TD>4</TD><TD>11/14/2023</TD><TD>11/18/2023</TD></TR><TR><TD>Direct</TD><TD>E5678</TD><TD>Bluey</TD><TD>REG</TD><TD>22.5</TD><TD>6/12/2024</TD><TD>6/15/2024</TD></TR><TR><TD>Overhead</TD><TD>E5678</TD><TD>Bluey</TD><TD>REG</TD><TD>1.5</TD><TD>6/12/2024</TD><TD>6/15/2024</TD></TR><TR><TD>Direct</TD><TD>E007</TD><TD>Bond James</TD><TD>REG</TD><TD>3.25</TD><TD>8/7/2024</TD><TD>8/10/2024</TD></TR><TR><TD>Overhead</TD><TD>E007</TD><TD>Bond James</TD><TD>REG</TD><TD>4</TD><TD>8/7/2024</TD><TD>8/10/2024</TD></TR><TR><TD>Direct</TD><TD>E007</TD><TD>Bond James</TD><TD>REG</TD><TD>21.25</TD><TD>1/24/2025</TD><TD>1/25/2025</TD></TR><TR><TD>Overhead</TD><TD>E007</TD><TD>Bond James</TD><TD>REG</TD><TD>7.25</TD><TD>1/24/2025</TD><TD>1/25/2025</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>ORG</P><TABLE border="1"><TBODY><TR><TD>EMP ID No</TD><TD>Person</TD><TD>Grade</TD><TD>Position</TD><TD>Div</TD><TD>Branch</TD><TD>Team</TD><TD>Start</TD><TD>End</TD></TR><TR><TD>E1234</TD><TD>Minnie Mouse</TD><TD>12</TD><TD>Specialist</TD><TD>Div A</TD><TD>Facility</TD><TD>FRR</TD><TD>11/17/2024</TD><TD>9/30/2025</TD></TR><TR><TD>E5678</TD><TD>Bluey</TD><TD>13</TD><TD>Lead&nbsp;&nbsp;</TD><TD>Div A</TD><TD>Med 1</TD><TD>Reception</TD><TD>5/5/2024</TD><TD>9/30/2025</TD></TR><TR><TD>E1224</TD><TD>Christmas Eve</TD><TD>09</TD><TD>Specialist</TD><TD>Div A</TD><TD>Med 2</TD><TD>OR</TD><TD>11/17/2024</TD><TD>9/30/2025</TD></TR><TR><TD>E007</TD><TD>Bond James</TD><TD>12</TD><TD>Specialist</TD><TD>Div B</TD><TD>Support</TD><TD>SPC</TD><TD>10/20/2024</TD><TD>9/30/2025</TD></TR><TR><TD>E1122</TD><TD>Elizabeth Doe</TD><TD>13</TD><TD>Specialist</TD><TD>Div B</TD><TD>Sales</TD><TD>OEA</TD><TD>1/26/2025</TD><TD>9/30/2025</TD></TR><TR><TD>E1985</TD><TD>Marty McFly</TD><TD>09</TD><TD>Nurse</TD><TD>Div C</TD><TD>Med 2</TD><TD>OR</TD><TD>5/7/2023</TD><TD>9/30/2025</TD></TR><TR><TD>E9999</TD><TD>Joe Smo</TD><TD>14</TD><TD>Mgr</TD><TD>Div C</TD><TD>Operations</TD><TD>OTB</TD><TD>12/31/2023</TD><TD>9/30/2025</TD></TR><TR><TD>E1122</TD><TD>Elizabeth Doe</TD><TD>12</TD><TD>Specialist</TD><TD>Div B</TD><TD>Support</TD><TD>AE</TD><TD>11/17/2024</TD><TD>1/25/2025</TD></TR><TR><TD>E1234</TD><TD>Minnie Mouse</TD><TD>11</TD><TD>Specialist</TD><TD>Div A</TD><TD>Facility</TD><TD>FRR</TD><TD>5/21/2023</TD><TD>11/16/2024</TD></TR><TR><TD><P>&nbsp;</P><P>E1224</P></TD><TD>Christmas Eve</TD><TD>07</TD><TD>Specialist</TD><TD>Div A</TD><TD>Med 2</TD><TD>TRI</TD><TD>5/5/2024</TD><TD>11/16/2024</TD></TR><TR><TD>E1122</TD><TD>Elizabeth Doe</TD><TD>09</TD><TD>Specialist</TD><TD>Div B</TD><TD>Support</TD><TD>AE</TD><TD>6/20/2021</TD><TD>11/16/2024</TD></TR><TR><TD>E3333</TD><TD>Krispie Kreme</TD><TD>13</TD><TD>Lead&nbsp;&nbsp;</TD><TD>Div B</TD><TD>Support</TD><TD>AE</TD><TD>5/9/2021</TD><TD>10/19/2024</TD></TR><TR><TD>E007</TD><TD>Bond James</TD><TD>11</TD><TD>Specialist</TD><TD>Div B</TD><TD>Elect Tech</TD><TD>UMS</TD><TD>5/7/2023</TD><TD>10/19/2024</TD></TR><TR><TD>E5678</TD><TD>Bluey</TD><TD>13</TD><TD>Specialist</TD><TD>Div A</TD><TD>Med 1</TD><TD>Clinic</TD><TD>10/1/2019</TD><TD>5/4/2024</TD></TR><TR><TD>E1224</TD><TD>Christmas Eve</TD><TD>07</TD><TD>Specialist</TD><TD>Div A</TD><TD>Tech Ops</TD><TD>NIT</TD><TD>3/26/2023</TD><TD>5/4/2024</TD></TR><TR><TD>E9999</TD><TD>Joe Smo</TD><TD>14</TD><TD>Mgr</TD><TD>Div A</TD><TD>Tech Ops</TD><TD>TOP</TD><TD>3/26/2023</TD><TD>12/30/2023</TD></TR><TR><TD>E1234</TD><TD>Minnie Mouse</TD><TD>11</TD><TD>Specialist</TD><TD>Div A</TD><TD>Maintenance and Services Support</TD><TD>DLA</TD><TD>1/30/2022</TD><TD>5/20/2023</TD></TR><TR><TD>E007</TD><TD>Bond James</TD><TD>09</TD><TD>Specialist</TD><TD>Div B</TD><TD>Environmental</TD><TD>ENT</TD><TD>12/6/2021</TD><TD>5/6/2023</TD></TR><TR><TD>E1985</TD><TD>Mary McFly</TD><TD>07</TD><TD>LPN</TD><TD>Div C</TD><TD>Med 1</TD><TD>Clinic</TD><TD>1/30/2022</TD><TD>5/6/2023</TD></TR></TBODY></TABLE> Wed, 12 Feb 2025 20:03:50 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Creating-measure-between-two-table-to-track-hours-in-position/m-p/4407648#M175010 Ellen_horton14 2025-02-12T20:03:50Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Creating-measure-between-two-table-to-track-hours-in-position/m-p/4407990#M175027 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="928711" data-lia-user-login="Ellen_horton14" class="lia-mention lia-mention-user">Ellen_horton14</a>&nbsp;,</P> <P>&nbsp;</P> <P>I think you can try code as below to create a measure.</P> <LI-CODE lang="markup">Labor hour base on Period = CALCULATE ( SUM ( LaborDetail[Labor Hours] ), FILTER ( LaborDetail, LaborDetail[EMP ID No] = MAX ( ORG[EMP ID No] ) &amp;&amp; AND ( LaborDetail[Pay Period End Date] &gt;= MAX ( ORG[Start] ), LaborDetail[Pay Period End Date] &lt;= MAX ( ORG[End] ) ) ) )</LI-CODE> <P>Result is as below.</P> <P><img /></P> <P>&nbsp;</P> <P>Best Regards,<BR />Rico Zhou</P> <P>&nbsp;</P> <P>If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.</P> Thu, 13 Feb 2025 02:49:19 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Creating-measure-between-two-table-to-track-hours-in-position/m-p/4407990#M175027 Anonymous 2025-02-13T02:49:19Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Creating-measure-between-two-table-to-track-hours-in-position/m-p/4408216#M175043 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="928711" data-lia-user-login="Ellen_horton14" class="lia-mention lia-mention-user">Ellen_horton14</a>&nbsp;</P> <P>Can you please try this measure:</P> <P>&nbsp;</P> <LI-CODE lang="markup">Hours_Worked_By_Assignment = VAR SelectedPayPeriod = MAX('LaborDetail'[Pay Period End Date]) -- Get the selected Pay Period VAR EmpID = SELECTEDVALUE('LaborDetail'[EMP ID No]) -- Get the employee ID VAR WorkDate = MAX('LaborDetail'[Work Date]) -- Get the Work Date -- Find matching assignment from ORG table VAR AssignedRole = CALCULATE( MAX('ORG'[Position]), 'ORG'[EMP ID No] = EmpID &amp;&amp; WorkDate &gt;= 'ORG'[Start] &amp;&amp; WorkDate &lt;= 'ORG'[End] ) -- Calculate total hours worked for the matching period VAR TotalHours = CALCULATE( SUM('LaborDetail'[Labor Hours]), 'ORG'[EMP ID No] = EmpID &amp;&amp; WorkDate &gt;= 'ORG'[Start] &amp;&amp; WorkDate &lt;= 'ORG'[End] ) RETURN IF(NOT(ISBLANK(AssignedRole)), TotalHours, BLANK()) -- Show hours only if assignment exists </LI-CODE> <P>&nbsp;</P> <P>&nbsp;</P> Thu, 13 Feb 2025 05:36:51 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Creating-measure-between-two-table-to-track-hours-in-position/m-p/4408216#M175043 Poojara_D12 2025-02-13T05:36:51Z