Forum Discussion
Helper IIPowerBI 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 changes from Month to Month and is unique to the Employee_ID. I need to use the Employee_ID look at the Entry_Date and determine the Hierarchy_ID based on the start/end date of the Employee_ID. Below is an example of what I'm trying to accomplish:
Any assistance is appreciated.
Thanks
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.
14 Replies
- MattAllington
Community Champion
You should not post the same question in 2 separate forums.
ss8551are you able to unmark this as a solution? I marked the incorrect post as a solution.
- AlB
Hi ss8551
This would be best done in PQ. Do share a sample of the tables in text-tabular format ( so that the contents can be copied) instead of on a screen cap and I will build the solution for you
If you want it in DAX, you can create a calculated column on Order_Table
Hierarchy_ID = LOOKUPVALUE ( Hierarchy_Table[Hierarchy_ID], Hierarchy_Table[Employee_ID], Order_Table[Employee_ID], Hierarchy_Table[EntryDate], Order_Table[Entry_Date] )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.
- ss8551
Hi AIB,
Thank you for your response. This is close, however, what I am looking to do is to pick the Hierarchy_ID based on a between dates. so for example in Jan Employeee ID had a Hierarchy_ID of 812046696 and in Feb the Hierarchy_ID was 887774292. What I'm looking to do is based on the Entry_Date choose the correct corresponding Hiearchy_ID and return that to the the Order_Table. Attached is a copy of the sample data and results. When i use the code you provided above no data is retunred in the column likely due to the order entery date not being equal to the hierarhcy date and needs to be between the Start_Date and End_Date.
Order_Table Hierarchy_Table Results to into Order Table Employee_ID Entry_Date Item Employee_ID Start_Date End_Date Hierarchy_ID Employee_ID Entry_Date Item Hierarchy_ID AB1234 1/1/2022 Widget123 AB1234 1/1/2022 1/31/2022 812046696 AB1234 1/1/2022 Widget123 812046696 CD5678 1/2/2022 Widget123 CD5678 1/1/2022 1/31/2022 862019607 CD5678 1/2/2022 Widget123 862019607 EF9012 1/3/2022 Widget124 EF9012 1/1/2022 1/31/2022 389491792 EF9012 1/3/2022 Widget124 389491792 AB1234 1/2/2022 Widget125 AB1234 2/1/2022 2/28/2022 887774292 AB1234 1/2/2022 Widget125 812046696 CD5678 1/3/2022 Widget123 CD5678 2/1/2022 2/28/2022 234349471 CD5678 1/3/2022 Widget123 862019607 EF9012 1/4/2022 Widget124 EF9012 2/1/2022 2/28/2022 657046417 EF9012 1/4/2022 Widget124 389491792 AB1234 2/4/2022 Widget125 AB1234 3/1/2022 3/15/2022 336341477 AB1234 2/4/2022 Widget125 887774292 CD5678 2/5/2022 Widget123 CD5678 3/1/2022 3/31/2022 363268779 CD5678 2/5/2022 Widget123 234349471 EF9012 2/6/2022 Widget123 EF9012 3/1/2022 3/31/2022 665378413 EF9012 2/6/2022 Widget123 657046417 AB1234 2/5/2022 Widget125 AB1234 2/5/2022 Widget125 887774292 CD5678 2/6/2022 Widget123 CD5678 2/6/2022 Widget123 234349471 EF9012 2/7/2022 Widget123 EF9012 2/7/2022 Widget123 657046417 AB1234 3/5/2022 Widget124 AB1234 3/5/2022 Widget124 336341477 CD5678 3/6/2022 Widget125 CD5678 3/6/2022 Widget125 363268779 EF9012 3/7/2022 Widget123 EF9012 3/7/2022 Widget123 665378413 AB1234 3/6/2022 Widget124 AB1234 3/6/2022 Widget124 336341477 CD5678 3/7/2022 Widget125 CD5678 3/7/2022 Widget125 363268779 EF9012 3/8/2022 Widget123 EF9012 3/8/2022 Widget123 665378413 - AlB
Try this
Hierarchy_ID = VAR entryDate_ = Order_Table[Entry_Date] RETURN CALCULATE ( DISTINCT ( Hierarchy_Table[Hierarchy_ID] ), 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.
- Vijay_A_Verma
Most Valuable Professional
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQyNDI2UdJRMtQ31DcyMDICMsOVYnWilZxdTM3MLcAyRjAZV7AMXI+RvglMJlApNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee_ID = _t, Entry_Date = _t, Item = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee_ID", type text}, {"Entry_Date", type date}, {"Item", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(Hierarchy_Table, (x)=> x[Start_Date]<=[Entry_Date] and x[End_Date]>=[Entry_Date] and x[Employee_ID]=[Employee_ID]){0}[Hierarchy_ID]) in #"Added Custom"Code for Hierarchy_Table (You would need to name this query as Hierarchy_Table)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnWzNDA0UtJRMtQ31DcyMIIwjeFsSwulWJ1oJUcnQyNjE9zKTM1QlRkhlBnpG1nA2cZgZc4upmbmFrhNAzooNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee_ID = _t, Start_Date = _t, End_Date = _t, Hierarchy_ID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee_ID", type text}, {"Start_Date", type date}, {"End_Date", type date}, {"Hierarchy_ID", Int64.Type}}) in #"Changed Type" - ss8551
Matt,
Many thanks. I was not aware of the protocol and will avoid doing this onwards. Was looking for a response and just thougth it would reach different audiences. I appreciate you pointing this out to me.
