Using more than one relationship

[Marra13]

I have two tables as follows:

Table1 AccountID PackageID Company                     Table2 AccountID PackageID

           11              51              A                                             11              51

           Null           52              B                                             12              52

           13              Null           C                                             13              53

           14              54              D                                             14              54

The data is messy hence nulls in table1

I have defined two relationships; AccountID : AccountID and PackageID : PackageID, the first one is active and, obviously, the second one is inactive.

Company is a filter, so, if I select company A the first row displays in both tables, however if I select company B only the second row displays in Table1, how do I effectively get PowerBI to use both relatonships? i.e. for Company B the AccountID relationships returns nothing but the PackageID relationship returns a row, and, if possible, where both relationships return a row only one is displayed rather than two? a unique or distinct operation is applied.

Thanks

[Marra13]

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.

[visheshjain]

Hi @Marra13,

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

[Marra13]

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.

[visheshjain · Accepted solution]

Hi @Marra13,

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

[Marra13]

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 company B.

[Shreeram04]

Hi @Marra13,

Please find the below screenshot and measure for your reference.

Measure 10 = CALCULATE(COUNT('Table (5)'[Company ]),USERELATIONSHIP('Table (5)'[PackageID],'Table (6)'[PackageID]))

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Thanks,