cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ss8551
Helper II
Helper II

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 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:

ss8551_0-1654289891811.png

Any assistance is appreciated.


Thanks

3 ACCEPTED SOLUTIONS

You should not post the same question in 2 separate forums. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

@ss8551 

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_
    )

 

 

SU18_powerbi_badge

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.

 

View solution in original post

@ss8551 

You're welcome

You have marked the wrong post as solution. Can you update this?

 

SU18_powerbi_badge

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.

 

View solution in original post

14 REPLIES 14
ss8551
Helper II
Helper II

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. 

Vijay_A_Verma
Super User
Super User

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"
AlB
Super User
Super User

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]
)

 

SU18_powerbi_badge

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.

 

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_IDEntry_DateItem Employee_IDStart_DateEnd_DateHierarchy_ID Employee_IDEntry_DateItemHierarchy_ID
AB12341/1/2022Widget123 AB12341/1/20221/31/2022812046696 AB12341/1/2022Widget123812046696
CD56781/2/2022Widget123 CD56781/1/20221/31/2022862019607 CD56781/2/2022Widget123862019607
EF90121/3/2022Widget124 EF90121/1/20221/31/2022389491792 EF90121/3/2022Widget124389491792
AB12341/2/2022Widget125 AB12342/1/20222/28/2022887774292 AB12341/2/2022Widget125812046696
CD56781/3/2022Widget123 CD56782/1/20222/28/2022234349471 CD56781/3/2022Widget123862019607
EF90121/4/2022Widget124 EF90122/1/20222/28/2022657046417 EF90121/4/2022Widget124389491792
AB12342/4/2022Widget125 AB12343/1/20223/15/2022336341477 AB12342/4/2022Widget125887774292
CD56782/5/2022Widget123 CD56783/1/20223/31/2022363268779 CD56782/5/2022Widget123234349471
EF90122/6/2022Widget123 EF90123/1/20223/31/2022665378413 EF90122/6/2022Widget123657046417
AB12342/5/2022Widget125      AB12342/5/2022Widget125887774292
CD56782/6/2022Widget123      CD56782/6/2022Widget123234349471
EF90122/7/2022Widget123      EF90122/7/2022Widget123657046417
AB12343/5/2022Widget124      AB12343/5/2022Widget124336341477
CD56783/6/2022Widget125      CD56783/6/2022Widget125363268779
EF90123/7/2022Widget123      EF90123/7/2022Widget123665378413
AB12343/6/2022Widget124      AB12343/6/2022Widget124336341477
CD56783/7/2022Widget125      CD56783/7/2022Widget125363268779
EF90123/8/2022Widget123      EF90123/8/2022Widget123665378413

 

@ss8551 

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_
    )

 

SU18_powerbi_badge

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.

 

Hi AIB,  

Thank you for your response.  I tried the above code but i'm receiving an error message,  A table of multiple values was supplied where a single value was expected.

@ss8551 

I forgot to add the employee ID

Hierarchy_ID =
VAR entryDate_ = Order_Table[Entry_Date]
VAR employeeID_= Order_Table[Employee_ID]
RETURN
    CALCULATE (
        DISTINCT ( Hierarchy_Table[Hierarchy_ID] ),
        Hierarchy_Table[Employee_ID] = employeeID_,
        Hierarchy_Table[Start_Date] <= entryDate_,
        Hierarchy_Table[End_Date] >= entryDate_
    )

SU18_powerbi_badge

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?

@ss8551 

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_
    )

 

 

SU18_powerbi_badge

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.

@ss8551 

You're welcome

You have marked the wrong post as solution. Can you update this?

 

SU18_powerbi_badge

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.

 

Done.  Sorry thank you again.

You should not post the same question in 2 separate forums. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

are you able to unmark this as a solution?  I marked the incorrect post as a solution.  

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors