Forum Discussion
Solved
PowerBI Joining UnRelated Tables
I have two tables in Power BI. I need to return the value from one table based on the the date range and employee_id of the original table. Can anyone assist me in how to do this? the Hierarchy_ID ch...
You should not post the same question in 2 separate forums.
It does work on the data you have shared (see attached file)
The error you are getting means there is more than one hierarchy for the given date and employee. So you should either:
1) solve the issue on the data or
2) define a criterion to select which of the matching hierarchies you want
If you go for 2), you'll have to update the code to implement this. For instance if you want to keep the largest one:
Hierarchy_ID = VAR entryDate_ = Order_Table[Entry_Date] VAR employeeID_= Order_Table[Employee_ID] RETURN CALCULATE ( MAX ( Hierarchy_Table[Hierarchy_ID] ), Hierarchy_Table[Employee_ID] = employeeID_, Hierarchy_Table[Start_Date] <= entryDate_, Hierarchy_Table[End_Date] >= entryDate_ )Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
