Forum Discussion
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.
Solution SupplierLooking 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.
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-salary/m-p/398291#M181818 which I've been using now. But I'll check out your solution as well, thanks!