Problem with relationships by 2 columns

[Anonymous]

Hello, 

I have the following structure and and I don't know how to proceed. A have 3 tables - Work Orders, Products and Consumption orders. I have a relationship between them by Part_Number. So, if a make a visual table or matrix, I can filter by Part_Number.

I want to be able to filter (slice) also by Document_No. So I made a Reference from Work Orders table and removed duplicates. 

The first relationship is ok but when I try to make the second relationship - it doesn’t allow and I got the following error:

How to proceed with this case? I want just to be able to filter(slice) by 2 columns - Part_Number and Document_Number.

Thanks in advance!

[amitchandak]

@Anonymous , You have too many bidirectional relations. They creating a loop. Try to have 1-M relation with a single direction.

As of now because of bi-directional relation, there are two paths to reach from one table to another, that is creating a problem.

[AilleryO · Accepted solution]

Hi,

As far as I know you have 2 approach to face your problem.

1. One could be using a reference table (works like an alias table), wich allows you to ge the 2 relations you require.

2. The second would be to have one of the 2 relations inactive (dashed line instead of plain line) and the use the USERELATIONSHIP() function in your measure to "activate" the relation when needed.

Hope this helps,

[Anonymous]

@AilleryO  Can you explain the 2 methods with more details or better - with example with my data?

First method - Ref_Table_Document_No - is that what you meant by reference table? So I have it, but it does not allow me to make the second relation.
Second  method - I've never used USERELATIONSHIP function.

[AilleryO]

Hi,

Regarding the first method Reference Table :

Right click on a Query, and you'll be able to Duplicate it, or create a Reference to it. A reference is like an alias, it doesn't not really duplicate your Table but make a "reference" to it. So any changes to the original table is reflected in the Reference table.

Once you have 2 tables instead of one, each one of them will have one relationship, instead of 2.

Regarding solution 2, the USERELATIONSHIP function makes you able to get results from an inactive relationship (exactly what you have with your relation in dashed lines on your screen copy). To know more about this function, you can watch this video :

https://www.youtube.com/watch?v=sONvctPlplY

It is really well explained with a case not so far from yours. It is a very helpful function and you'll certainly be helped in many cases thanks to this function.

Solution 2 seems the best solution in your case.

 I do agree with @amitchandak, I think as well you should reduce (if not suppress) the number of bi directionnal filters. 

Hope this helps

[Anonymous]

@AilleryO  thanks a lot, USERELATIONSHIP worked fine, now i got what I wanted.
@amitchandak I have tried to make all relations single, not bi-directional, but it's not working. I cannot slice by the data in the Reference table, because it filters only the the data,. but i can see all products.