Forum Discussion

Yggdrasill's avatar
Yggdrasill
Responsive Resident
4 years ago

Find value between two tables and dates

Maybe it's because it's monday or something but I just can't get my head around a what I thought was a simple stuff but here I am, few hours later... Problem: I have two tables with no active r...
Need Help
Show and Tell
sevenhills's avatar
sevenhills
Super User
4 years ago

I tried this and see if it works for you

 

 

 

No relationships created

Measure

Rate_Hourly =  
var _Rate = MAXX( 
    FILTER( SalaryTable, SalaryTable[EmployeeID] = SELECTEDVALUE(FactTable[EmployeeID])
                     && SalaryTable[SalaryFromDate] <= SELECTEDVALUE(FactTable[Date]))
                     , SalaryTable[Rate]
) 
return _Rate

 

FYI: I prefer to have a calculated column than measure if it is a large dataset

 

... 

Yggdrasill's avatar
Yggdrasill
Responsive Resident
4 years ago

sevenhills Thanks for the prompt reply and taking the time to help out!

I've successfully implemented the DAX code and it works. However it breaks at another granular level when I'm not looking at dates. See how the total does not sum up


Can I somehow make this work without having to use ISINSCOPE() or other similar functions?

Or should I just do as you mentioned and use a column?

Kind regards