PowerBI Joining UnRelated Tables

[ss8551]

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

[MattAllington · Accepted solution]

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

[AlB · Accepted solution]

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

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]

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]

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]

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]

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

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

[AlB]

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

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]

AIB,

I am still receiving an error message:  A table of multiple values was supplied where a single value was expected.  Any toughts?

[AlB · Accepted solution]

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

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]

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.

[AlB · Accepted solution]

@ss8551 

You're welcome

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

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]

Done.  Sorry thank you again.

[MattAllington · Accepted solution]

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

[ss8551]

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