Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ss8551
Helper II
Helper II

PowerBI Joining UnRelated Tables

‎06-03-2022 02:01 PM

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

Labels:
Message 1 of 15
1,636 Views
0
3 ACCEPTED SOLUTIONS
MattAllington
MVP

‎06-03-2022 08:13 PM

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


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

View solution in original post

Message 2 of 15
1,573 Views
0
AlB
Super User
Super User
In response to ss8551

‎06-09-2022 07:39 AM

@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

PowerBI Joining UnRelated Tables.pbix
Message 10 of 15
1,432 Views
AlB
Super User
Super User
In response to ss8551

‎06-09-2022 07:56 AM

@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

Message 12 of 15
1,418 Views
0
14 REPLIES 14
ss8551
Helper II
Helper II

‎06-06-2022 07:58 AM

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. 

Message 15 of 15
1,525 Views
0
Vijay_A_Verma
Super User
Super User

‎06-03-2022 11:56 PM

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"
Message 14 of 15
1,561 Views
0
AlB
Super User
Super User

‎06-03-2022 11:52 PM

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.

 

Message 4 of 15
1,563 Views
0
ss8551
Helper II
Helper II
In response to AlB

‎06-06-2022 08:28 AM

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

 

Message 5 of 15
1,521 Views
0
AlB
Super User
Super User
In response to ss8551

‎06-08-2022 10:50 AM

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

 

Message 6 of 15
1,470 Views
0
ss8551
Helper II
Helper II
In response to AlB

‎06-08-2022 04:36 PM

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.

Message 7 of 15
1,460 Views
0
AlB
Super User
Super User
In response to ss8551

‎06-08-2022 11:20 PM

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

 

Message 8 of 15
1,449 Views
0
ss8551
Helper II
Helper II
In response to AlB

‎06-09-2022 07:18 AM

AIB,

 

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

Message 9 of 15
1,438 Views
0
AlB
Super User
Super User
In response to ss8551

‎06-09-2022 07:39 AM

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

 

PowerBI Joining UnRelated Tables.pbix
Message 10 of 15
1,433 Views
ss8551
Helper II
Helper II
In response to AlB

‎06-09-2022 07:47 AM

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.

Message 11 of 15
1,426 Views
0
AlB
Super User
Super User
In response to ss8551

‎06-09-2022 07:56 AM

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

 

Message 12 of 15
1,419 Views
0
ss8551
Helper II
Helper II
In response to AlB

‎06-09-2022 08:16 AM

Done.  Sorry thank you again.

Message 13 of 15
1,404 Views
0
MattAllington
MVP

‎06-03-2022 08:13 PM

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


* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Message 2 of 15
1,574 Views
0
ss8551
Helper II
Helper II
In response to MattAllington

‎06-09-2022 08:16 AM

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

Message 3 of 15
1,404 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All