Forum Discussion
Problem with relationships by 2 columns
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!
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,
5 Replies
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,
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.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 , 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.
