Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 red
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.
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?
Solved! Go to Solution.
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.
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:
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.
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.
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.
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:
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.
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.
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.
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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |