Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Inactive Relationships in Power Bi between many tables

Having issues creating relationship between between two tables set in large data model consisting of more than 20 tables. Currently Trying connecting these tables so I can display the data in table. The current active relationships I have linked in redAnnotation 2020-01-20 1343582.png

 

I would like to create active relationship the Workorder Table to the Incident table but Power BI is not allowing me due to the link between the Account Table and the Incident table.

 

 

Annotation 2020-01-20 134358.png

 

I simply want to display the information in visual table showing the incident id, account name and the workorder name for example without sacrificing the active relationships created in from Accounts table  -> Customer Assets table -> Agreements Table  -> WorkOrders Table.

 

Would something like an USERELATIONSHIP work in this case?

 

 

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your description, If you have a table pattern like this, with loops, 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.

b1.png

 

Power BI Desktop won’t allow a relationship to be set as Both if it will create ambiguity in reports. There are several different ways you can deal with this and 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. This makes the pattern of relationships like a star schema. With a star schema, all of the relationships can be set to Both.

 

It is suggested that you may use a star chema as below. Cross filtering both directions works well for a pattern of table relationships that look like the pattern.

b2.png

 

For further information, you may refer to the following link.

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

 

Best Regards

Allan

 

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

 

View solution in original post

3 REPLIES 3
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your description, If you have a table pattern like this, with loops, 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.

b1.png

 

Power BI Desktop won’t allow a relationship to be set as Both if it will create ambiguity in reports. There are several different ways you can deal with this and 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. This makes the pattern of relationships like a star schema. With a star schema, all of the relationships can be set to Both.

 

It is suggested that you may use a star chema as below. Cross filtering both directions works well for a pattern of table relationships that look like the pattern.

b2.png

 

For further information, you may refer to the following link.

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

 

Best Regards

Allan

 

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

 

Anonymous
Not applicable

Thanks for the responses. I added another workorder table and changed the name and use this table as a lookup and this method has worked. 

Thanks.

amitchandak
Super User
Super User

You should not have loops or two paths to reach a table.

From Table A to Table B, I have a direct connection. I also Can reach via Table C. Moving from 1 to Many directions.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.