Multiple relationships between tables

[Anonymous]

I've got my table 'Item', which has 6 fields I need to relate to another table: 'Item Category 1' to 'Item Category 6'.  
All these 6 category fields need to be joined to the table 'Item Category'. I started by making a relation between Item.Category1 to Item Category.Code and did this for all 6 categories.

For some reason I can't seem to get all these 6 relations in place, since only 1 can be active. 
Could someone help me on my way to get all 6 working? The first relation is working properly. 

P.S.: In SQL the query I would make the result would look like this:

SELECT *
FROM Item i 
LEFT OUTER JOIN  Item Category ice1 on  i.category 1 = ice1.code

LEFT OUTER JOIN  Item Category ice2 on  i.category 2 = ice2.code

LEFT OUTER JOIN  Item Category ice3 on  i.category 3 = ice3.code

LEFT OUTER JOIN  Item Category ice4 on  i.category 4 = ice4.code

LEFT OUTER JOIN  Item Category ice5 on  i.category 5 = ice5.code

LEFT OUTER JOIN  Item Category ice6 on  i.category 6 = ice6.code

[LivioLanzo · Accepted solution]

Hi @Anonymous

you need to unpivot both tables and show the category only one column, and then relate the tables by this column

[v-chuncz-msft · Accepted solution]

@Anonymous ,

You may also take advantage of USERELATIONSHIP in DAX.

[LivioLanzo · Accepted solution]

Hi @Anonymous

you need to unpivot both tables and show the category only one column, and then relate the tables by this column