I need multiple relationships with tables like in SQL but Power BI keeps saying I can only have 1

[psychobunny83]

I'm not really sure how to handle this.  In SQL we have many relationships between multiple tables.  For example:

Company < Company_Locations < Master_Products

Company < Opportunities < Master_Products

In the above example, one Company can have 1 or many Company_Locations, as well as Opportunities.  One Company_Location can have 1 or many Master_Products, and one Opportunity can also have 1 or many Master_Products.

100% of Master_Products will have a Company_Location, but only 25% or so of Master_Products will have an Opportunity.

The above is how I have it set up in SQL, so a Master_Product relates to a Company either via the Company_Location table, or the Opportunities table.

In Power BI though, it only shows the one with Opportunities as Active, with the one with Company Locations inactive.  If I try to make it active, it says I have to remove another relationship between Companies and Master Products first, but I don't want to do that.

Some reports are for Master Products and need to show data from their related Company_Location, but other reports are for Master Products and need dto show data from the Opportunity, and in some cases it needs to be both.

This is fine in our CRM and in SQL Server, but it's just Power BI that's saying I can't have more than 1 relationship.  

In another example, the Opportunities table is related to the Users table, but multiple times:

Owner_ID is the ID from the User table of the person that the record is assigned to.

Created_by_ID is the ID from the User table of the person that made the record.

Specialist_ID is the ID from the User table of the person that is the specialist.

And so on, but in Power BI relationships it shows all of these as inactive, and if I try to make them active it says it will create ambiguity with some other table.

What do I do in this case?

[v-alq-msft]

Hi, @psychobunny83 

I'd like to suggest you use a star schema as below where cross filtering both directions works well.

If you have a table pattern with loops as follows, then cross filtering can create an ambiguous set of relationships. For instance, if you sum up a field from TableX and then choose to filter by a field on TableY, then it’s not clear how the filter should travel, through the top table or the bottom table.

As with active/inactive relationships, Power BI Desktop won’t allow a relationship to be set to Both if it will create ambiguity in reports. There are several different ways you can handle this situation. Here are the two most common:

• Delete or mark relationships as inactive to reduce ambiguity. Then, you might be able to set a relationship cross filtering as Both.
• Bring in a table twice (with a different name the second time) to eliminate loops. Doing so makes the pattern of relationships like a star schema. With a star schema, all of the relationships can be set to Both.

For further information, I'd like to suggest you refer to the document .

Best Regards

Allan

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

[psychobunny83]

Hi everyone thanks so much for the suggestions.

@v-alq-msft Adding the tables twice seems to be what I was missing.  Is that safe to do and standard practice?  When I used to use FileMaker years ago that's how it worked, but it would just automatically create multiple versions of the same table. When I just tried that now, I added a second instance of the Company table, and then related that to Opportunities and now everything is working as expected.

[v-alq-msft]

Hi, @psychobunny83 

It is suggested to use a star schema . It is a mature modeling approach widely adopted by relational data warehouses. It requires modelers to classify their model tables as either dimension or fact.

Best Regards

Allan

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

[amitchandak]

@psychobunny83 , seem like a case of use relation -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[Greg_Deckler]

@psychobunny83 - You can have multiple relationships in Power BI but only one can be "active". To get around this, use USERELATIONSHIP to use an inactive relationship in a measure. Or, refactor your data model. Hard to tell which might be better. 

[nandic]

@psychobunny83 , if needed to create multiple relationships between tables, create composite key (combine these columns in one column) and create join based on this column because Power BI allows only one column for join.
On the other hand, if you have columns which can not be concatenaded (ie: order date, ship date, delivery date), you can create join by each of these and always only one join will be active. But there is function UseRelationship which allows you to change join in your Dax calculation.