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

[ChuckS]

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

[HotChilli · Accepted solution]

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]

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.

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]

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