Forum Discussion
Using more than one relationship
Hi Anonymous,
Ideally, *:* relationship should not be used, until you know what you are doing, as it might provide unreliable results and slow down the report as well.
So here is my solution and hopefully it works for you.First of all I have created a new table with the distinct values of companies from table 1, aka Company, making it a sort of Dimension table.
Next I created a company column in table 2 using lookup, based on accountID and packageID.
So this will run on the assumption that either accountID or package ID will be present in table 1.
Then I created a 1:* relationship between Company and Tables1&2 and put that column as a slicer on the visual.
Here is the result:
I have attached my file for your reference.
I hope this solves your issue and is in a way combining both the tables, so you probably don't need to use Table1 anymore.If this solves your problem, please do mark it as the solution and/or kudo it so that others can reach the solution faster.
Thank you,
Vishesh Jain
Ammendment:
This is not what I want, the tables I've defined are data tables and I want to union the rows from the two tables depending on which company is selected in the filter, so if Company A is selected row 1 from both tables 1 and 2 are displayed, however if company B is selected only row 2 from table 1 is displayed because AccountID is null and the active relationship doesn't work, I'd like to use the inactive relationship but I can't get it to display just the rows from table 2 of PackageID 52.
Hi Anonymous,
Please can you confirm the direction of your realtionships i.e. is Table1 filtering Table2 or the other way round.
Also, what kind of realtionship are you using, 1:1, 1:* or *:*
Thank you,
Vishesh Jain
Table1 is filtering table2. The relationship is *:*, like I said, the data is very messey and the purpose of the report is to aid tidying.
Thanks.
Hi Anonymous,
Ideally, *:* relationship should not be used, until you know what you are doing, as it might provide unreliable results and slow down the report as well.
So here is my solution and hopefully it works for you.First of all I have created a new table with the distinct values of companies from table 1, aka Company, making it a sort of Dimension table.
Next I created a company column in table 2 using lookup, based on accountID and packageID.
So this will run on the assumption that either accountID or package ID will be present in table 1.
Then I created a 1:* relationship between Company and Tables1&2 and put that column as a slicer on the visual.
Here is the result:
I have attached my file for your reference.
I hope this solves your issue and is in a way combining both the tables, so you probably don't need to use Table1 anymore.If this solves your problem, please do mark it as the solution and/or kudo it so that others can reach the solution faster.
Thank you,
Vishesh Jain