Creating a relationship with multiple columns

[Hudg16]

Hello,
I am trying to attach several columns in one table to one column in another.

Here on the "Accessory" table, we can have up to 3 devices, in the three columns.

To give context the accessory table comes from a larger -- OTTERBOX CASE IPHONE 6+/7+/8+, QUANTITY, SALES, etc. The device names are just parsed from that but still need to remain attached to the quantity and other values.

On the device table, I only have one device.

I want to it to be so that, if you select an iphone 12 in the device table is linked to an iphone 12 in the accessory table. Or an IPHONE 12PRO to IPHONE12PRO. My previous solution to this was transposing the accessories with a many to many relationship, such that (iphone6+,7+,8+ would all be in the same column) but this duplicated, and tripled the quantity of accessories I had (since each row is an accessory, with its associated device), with a single row of Device Type in the accessory table.

I understand I should be using some form of merged column for the accessory table, but I am lost on how I can then create this relationship to the device table properly.

[V-pazhen-msft]

@Hudg16 

Use the new measures in the pbix:

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Hudg16]

@V-pazhen-msft 
But if I am looking to attach quantities that belong to table 2 (device out) versus a given selection from the find device is this possible. 

[V-pazhen-msft]

@Hudg16 
I doubt the alternative you suggestion is possible, relationship can only connect the exact cell content between column and another column. You may create a merged column and use find() function in dax to identify whether which row device can be found, but the problem you can make it display like your expected output.

PBIX attached. 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Hudg16]

Hi @V-pazhen-msft 
This is good, I missed a key detail and must apologize. 

If in "Table" we have both an iPhone 12 and iPhone 12pro, when this row is selected is it possible for both an iPhone 12 and iPhone 12pro in the device output (from Table2)? 

[V-pazhen-msft]

@Hudg16 

You may create an active many to 1 relationship using Device 1 with Device out. And create 2 inactive relationship for Device 2 and Device 3, then create measures to achieve the expected interaction. 

DV1 = MAX([Device1])

DV2 = CALCULATE(MAX([Device2]),USERELATIONSHIP('Table (2)'[Device out],'Table'[Device2]))

DV3 = CALCULATE(MAX([Device3]),USERELATIONSHIP('Table'[Device3],'Table (2)'[Device out]))

Check out the relationships and other detail in the pbix. 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Hudg16]

@V-pazhen-msft 
Seems very reasonable, do the measures help to imitate what would be an active relationship (if not for the other one existing?)
Also alternatively would it make any more (or less) sense if I merged those columns and delimited them? Is there any way to make a relationship based on a field containing something? So as to avoid having the several active and inactive relationships?
As well could you attach the pbix again? Thank you.