cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
patrisack Frequent Visitor
Frequent Visitor

Lookup value with two variables - need help

Hi!

 

I have an issue with with report I'm building for my company. I have a two tables that I want to combine. One with Salary listed per employee and another table with worked hours. Salaries for employees is listed for each salary period in a new row (from one date to another). Worked hours is listed per day and shows worked hours for each day per employee ID. There is also a table with information about the employee where employees are only listed once per row.

 

I would like to somehow get the correct salary from the "Salary" table to the "Worked hours" table, looking at the employee ID and the date when they worked. This is to then make a report for the total cost of personnel-costs.

 

Here is an example of the data (a simplified example of how the real data model looks).

 

Would really appriciate any help as I've been stuck for the last couple of days.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Lookup value with two variables - need help

Looking for something like this? (assuming HoursWorked and Salary are both related to Employees).

 

=VAR dt = HoursWorked[Date]
RETURN CALCULATE( 
       VALUES(Salary[Salary])
    	,Salary[FromDate] <= dt
    	,Salary[ToDate] > dt || Salary[ToDate] = BLANK()    
    	,ALLEXCEPT( HoursWorked, Employee[EmployeeID] )
 )

Note, the expression will return an error if more than one salary is found for a given employee at any time.

2 REPLIES 2

Re: Lookup value with two variables - need help

Looking for something like this? (assuming HoursWorked and Salary are both related to Employees).

 

=VAR dt = HoursWorked[Date]
RETURN CALCULATE( 
       VALUES(Salary[Salary])
    	,Salary[FromDate] <= dt
    	,Salary[ToDate] > dt || Salary[ToDate] = BLANK()    
    	,ALLEXCEPT( HoursWorked, Employee[EmployeeID] )
 )

Note, the expression will return an error if more than one salary is found for a given employee at any time.

Highlighted
patrisack Frequent Visitor
Frequent Visitor

Re: Lookup value with two variables - need help

Hi! I'm sorry that I didn't get back to you, I seem to have posted this question twice somehow (it's was my first post, probably did something wrong). Got a similar answer there: http://community.powerbi.com/t5/Desktop/Conditional-Lookup-Match-ID-and-dates-and-fetch-correct-sala... which I've been using now. But I'll check out your solution as well, thanks!