cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JorgenV Visitor
Visitor

Multiple relationships between tables

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

  

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Multiple relationships between tables

Hi @JorgenV

 

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Community Support Team
Community Support Team

Re: Multiple relationships between tables

@JorgenV ,

 

You may also take advantage of USERELATIONSHIP in DAX.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Super User
Super User

Re: Multiple relationships between tables

Hi @JorgenV

 

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Community Support Team
Community Support Team

Re: Multiple relationships between tables

@JorgenV ,

 

You may also take advantage of USERELATIONSHIP in DAX.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.