Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ChuckS
Helper I
Helper I

How to create a relationship where two columns in table A need to relate to one column in table B

Hello All,

 

I have been searching and trying all manner of things to get a unque relationship to work but so far it is not going well.

I have two tables. LOADS and DRIVERS.

LOADS has several columns but the two in question are Driver 1 and Driver 2. These fields list a unque Driver Id. Driver 1 will always have a value but Driver 2 is only populated occasionally.


DRIVERS list the individual driver details with one of the column being Driver Id.

How can I get Driver 1 and Driver 2 to both relate to the DRIVERS table to get the drivers specific details?

Merging the Driver 1 and 2 columns in the LOADS table is not an option as the Driver Id in the DRIVERS table is specific to the driver. 

 

Thanks for any assistance.

Chuck

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

I should have been clearer with my explanation.  Option 1 would involve Unpivotting the Driver columns in the Fact table in Power Query. So, effectively, 2 driver columns become 1.  The rows in the Fact table would duplicate (there's nothing wrong with that) and you would have to be aware of this when writing any measures.

--

You mention duplicating the Drivers table and yes, this is definitely an option.  I forgot about this technique because I almost never use it.  Occasionally with date fields, maybe.

--

And, lastly, you can stick with the data as it is but learn how to use the USERELATIONSHIP clause in measures.  It's a little bit different if you just want to retrieve name details from the dimension table for the 2nd driver.  You can probably use a LOOKUPVALUE for this. 

View solution in original post

3 REPLIES 3
HotChilli
Super User
Super User

I should have been clearer with my explanation.  Option 1 would involve Unpivotting the Driver columns in the Fact table in Power Query. So, effectively, 2 driver columns become 1.  The rows in the Fact table would duplicate (there's nothing wrong with that) and you would have to be aware of this when writing any measures.

--

You mention duplicating the Drivers table and yes, this is definitely an option.  I forgot about this technique because I almost never use it.  Occasionally with date fields, maybe.

--

And, lastly, you can stick with the data as it is but learn how to use the USERELATIONSHIP clause in measures.  It's a little bit different if you just want to retrieve name details from the dimension table for the 2nd driver.  You can probably use a LOOKUPVALUE for this. 

ChuckS
Helper I
Helper I

Thank you for replying. 

I do not understand the option 1 recommendation. Since a picture is worth a thousand words here is a mmockup of the tables and a visual.
Tables.jpgVisual.jpg

With the single relationship (Loads.Driver 1 -> Drivers.Driver Id), the visual can list the name for driver 1.  This however doesnot help in getting the name for driver 2. In the report the driver id numbers would not be listed, only their name. I added the id numbers just for clearity.

I considered duplicating the DRIVERS table. One for Driver 1 and a second for Driver 2 but I feel that is a hack work around not to mention the duplication of data. I prefer to learn the proper method to achieve this.

 

Thanks,

Chuck

HotChilli
Super User
Super User

2 options:

1. Use a DriversID column along with a Driver Number column.  Driver Number will be 1 or 2.

or

2. Use 2 driverID columns and create 2 relationships to dimension table (1 active, 1 inactive).  Depending on what measures you create, you can use USERELATIONSHIP keyword to get the relevant driver 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Kudoed Authors