DAX Calculated column based on two columns from other table

Question

I need to write a DAX statement which is somewhat complex from a conceptual/logical standpoint- so this might be hard to explain.

I have two tables.

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 each employee might get paid a different Wage for the same Function ( there is also a dimension for functions and employees ).

'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

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.

'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

(Note that wages can change over time)

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).

I'm really struggling with the logic steps, so I'm not sure what the best way to do this calculation is...

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.

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

HERE IS WHAT I HAVE SO FAR:

Step 1 = Filter 'Wages' table so that StartDate < CurrentRowDate 
Step 2 = Filter 'Wages' table so that EndDate > CurrentRowDate
Step 3 = LOOKUPVALUE( 'Wages'[Wage], 'Wages'[EmpFunID], Table2[EmpFunID])

Now I just need that converted into a DAX function.

Why don't you create a column that combines Employee ID and Function ID in both tables and use that to look up data? — teylyn
Just updated - the Wage value can change over time so on the Table 2, if I have Employee #1234 performing Function #20 for $18/hr on 1/4/2016 and then on 1/5/2016 that employee gets a raise to $22, we then have the same WageFunctionID combo with 2 different wage values. — SUMguy

Tamás Szabolcs Tamás Szabolcs · Accepted Answer · 2017-01-30T16:05:55

Not sure if got it totally right, but maybe something similar? If you put this into Table2 as a calculated column, it will transform the current row context of the Table2 into a filter context.

So SUMX will use the current row's data from Table2, and will do a sum on a filtered version of the wages table: wages table will be filtered by using the current date, employeeid and functionid from Table2, and for each row in the Table2 itt will only sum those wages, which are belong to the current row.

CALCULATE(
   SUMX(
     FILTER(
     'Wages',
     'Wages'[StartDate] >= 'Table2'[Date],
     'Wages'[EndDate] <= 'Table2'[Date],
     'Wages'[EmployeeId] = 'Table2'[EmployeeId],
     'Wages'[FunctionId] = 'Table2'[FunctionId]
     ),
    'Wages'[Wage]
)

DAX Calculated column based on two columns from other table

1 Answers