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

    • sevenhills's avatar
      sevenhills
      Super User
      4 years ago

      Please can you provide what is expected output in your top scenario (like a mockup)

       

      Tough to visualize sometimes ... 

      • Yggdrasill's avatar
        Yggdrasill
        Responsive Resident
        4 years ago

        The expected output is to find hourly rate and multiply that number with the amount of hours worked. That should define the cost. However I need to be able to look at cost regardless of if I'm looking at one employee, two or all.


         

        Come to think of it maybe it's easier to let PQ calculate the cost in the fact table? So everytime an hour is registered another column is calculated for the cost behind that hour