Forum Discussion
PowerBI Joining UnRelated Tables
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.
AIB,
I am still receiving an error message: A table of multiple values was supplied where a single value was expected. Any toughts?
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. |
AIB,
Thank you so much! I really appreciate your assistance. I chose option two and it did resolve. It is possible that a hierarchy code could change within the month but each one should be unique. I'll double check the data and see if I can find out the frequency of this occurance (should be small and may be acceptable to use max or min to resolve. I really appreaciate all of your assistance.
Done. Sorry thank you again.