title: DAX Calculated column based on two columns from other table
body: <p>I need to write a DAX statement which is somewhat complex from a conceptual/logical standpoint- so this might be hard to explain. </p>
  
  <p>I have two tables. </p>
  
  <p>On the first table (shown below) I have a list of numeric values (Wages). For each value I have a corresponding date range. I also have EmployeeID and FunctionID. The purpose of this table is to keep track of the hourly Wages paid to employees performing specific functions during specific date ranges. Each Function has it's own Wage on the Wage table, BUT <em>each employee might get paid a different Wage for the same Function</em> ( there is also a dimension for functions and employees ). </p>
  
  <pre><code>'Wages'
  
  Wage    StartDate    EndDate        EmployeeID     FunctionID
  20      1/1/2016     1/30/2016      3456           20
  15      1/15/2016    2/12/2016      3456           22
  27.5    1/20/2016    2/20/2016      7890           20
  20      1/21/2016    2/10/2016      1234           19
  </code></pre>
  
  <p>On 'Table 2' I have a record for every day that an Employee worked a certain Function. Remember, Table 1 contains the Wage information for every function.</p>
  
  <pre><code>'Table 2'
  
   Date        EmployeeID     FunctionID    DailyWage
   1/1/2016    1234           $20            =CALCULATE( SUMX( ??? ) )
   1/2/2016    1234           $20            =CALCULATE( SUMX( ??? ) )
   1/3/2016    1234           $22            see below
   1/4/2016    1234           $22
   1/1/2016    4567           $27
   1/2/2016    4567           $27
   1/3/2016    4567           $27
  </code></pre>
  
  <p>(Note that wages can change over time)</p>
  
  <p>What I'm trying to do is create a Calculated Column on 'Table 2' called 'DailyWage'. I want every row on 'Table 2' to tell me how much the EmployeeID was paid for the full day (assuming an 8 hour workday).</p>
  
  <p>I'm really struggling with the logic steps, so I'm not sure what the best way to do this calculation is...</p>
  
  <p>To make things worse, an EmployeeID might get paid a different Wage for the same Function on a different Date. They might start out at one wage working function X and then generally, their wage should go up a few months in the future... That means that if I try to concatenate the EmployeeID and the FunctionID, I won't be able to connect the tables on the concatenated value because neither table will have unique values.</p>
  
  <p>So in other words, if we CONCATENATE the EmployeeID and FunctionID into EmpFunID, we need to take the EmpFunID + the date for the current row and then say "take the EmpFunID in the current row, plus the date for the current row and then return the value from the Wage column on the Wages table that has the same EmpFunID AND has a StartDate less that the CurrentRowDate AND has an EndDate greater than the CurrentRowDate</p>
  
  <p>HERE IS WHAT I HAVE SO FAR:</p>
  
  <pre><code>Step 1 = Filter 'Wages' table so that StartDate &lt; CurrentRowDate 
  Step 2 = Filter 'Wages' table so that EndDate &gt; CurrentRowDate
  Step 3 = LOOKUPVALUE( 'Wages'[Wage], 'Wages'[EmpFunID], Table2[EmpFunID])
  </code></pre>
  
  <p>Now I just need that converted into a DAX function.</p>