How to fix Circular reference in Data Relationship

[EaglesTony]

Hi,

  I have a slicer that uses a column(Sprint) in a table(SprintDates).

  I have table1 that I calculated a column(Sprint) that works.

  I then(via a lookupValue) created a new column(SprintRelated) in table2 based off table1 that adds the table1 field called Sprint.

  The issue is when I try to add a relationship of SprintReleated in table2 to Sprint in SprintDates table and set it to the Cross-filter direction of both(I need this in order for the table to filter based on the slicer value), it is giving me "A circular dependency was detected ????

[EaglesTony]

ok,, so it kinda works.

I have the following tables:

1) BridgePIandSprint which has the PI and Sprint together as a "combokey"

2) BridgePITable(it is a child table to BridgePIAndSprint) and has a relationship based on PI

3) TimeInBacklogStatus which has the PI and Sprint together as a "combokey"

4) TimeInReady which has the PI and Sprint together as a "combokey"

5) I was able to establish a relationship between BridgePIandSprint and both TimeInBacklogStatus and TimeInReady

6) BridgeTeamAndArt which has the Team and ART together as a "combokey"

7) TimeInBacklogStatus which has the Team and ART together as a "combokey"

😎 TimeInReady which has the Team and ART together as a "combokey"

9) I was able to establish a relationship between BridgeTeamAndArt and TimeInBacklogStatus, however when I tried to establish the relationship between BridgeTeamAndArt and TimeInReady, I get:

"There are ambiguous paths between BridgePITable and BridgeTeam"

"BridgePITable->BridgePIAndSprintTable-TimeInBacklog->BridgeTeamAndARTTable-->BridgeTeam"

"BridgePITable->BridgePIAndSprintTable-TimeinReady->BridgeTeamAndARTTable-->BridgeTeam"

Not sure how to resolve this ?

[EaglesTony · Accepted solution]

All I figured this out, it had to do with setting up the relationships as Single instead of Both, which caused the amiguous error.

Basically, I have a top table called Team, I connected this as a single connection to the "child tables" based off a key (some of the fields in the child tables were also used in slicers) and this fixed the issue, so have to be careful with "both"

[Anonymous]

Thanks for the reply from pborah and saud968 , please allow me to provide another insight:

Hi, @EaglesTony 

Could you please let me know if the responses from pborah and saud968 have resolved your issue? If it did, kindly accept them as the solution.
 

You might also consider leaving the step of creating a new column in Power Query. This way, you won't encounter circular dependency errors when referencing this column.
 Below is a screenshot of the relevant documentation for custom columns in Power Query:

For more details, please refer to the following link:

Add a custom column in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,

Leroy Lu

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

[pborah]

A way to get around this is to create these tables in the transform data stage and then load everything and create relationships.

Additionally, do break the indirect relationships that Power BI automatically creates as mentioned by another user.

[saud968]

Understand the Circular Dependency:
A circular dependency occurs when two or more tables are interdependent, creating a loop that Power BI cannot resolve.

Review Your Relationships:
Check the relationships between SprintDates, table1, and table2. Ensure that there are no indirect relationships causing the circular dependency.

Break the Circular Dependency:
One way to break the circular dependency is to use a bridge table. This table will act as an intermediary to connect your tables without creating a loop.

Create a Bridge Table:
Create a new table (e.g., BridgeTable) that contains unique values of the Sprint column.
Establish relationships between BridgeTable and the other tables (SprintDates, table1, and table2).

Modify Relationships:
Set up the relationships as follows:
BridgeTable[Sprint] to SprintDates[Sprint]
BridgeTable[Sprint] to table1[Sprint]
BridgeTable[Sprint] to table2[SprintRelated]
Ensure that the cross-filter direction is set to single for these relationships to avoid circular dependencies.

Adjust Your Slicer:
Use the Sprint column from the BridgeTable in your slicer. This way, the slicer will filter all related tables without causing a circular dependency.

Here’s a simplified example of how you can create and use a bridge table in DAX:

BridgeTable = DISTINCT(UNION(SELECTCOLUMNS(SprintDates, "Sprint", SprintDates[Sprint]), SELECTCOLUMNS(table1, "Sprint", table1[Sprint])))

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[EaglesTony]

So there is no relationship between SprintDates and Table1 ?

I created the Bridge Table in PowerQuery, but duplicating the SprintDates table and removing the columns not needed.

[saud968]

Did creating a bridge table work for you?

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[EaglesTony]

Kindof.

I created a bridge table for PI and associated table1 and table2 to it and that worked.

I created another bridge table for Sprint and associated table1 to it and that worked, but when I tried to associate table2 to it I get "Ambiguous path between BridgePITable and BridgeSprintTable"

[saud968]

Use a Single Bridge Table:
Instead of using two separate bridge tables, consider combining them into a single bridge table if possible. This can simplify the relationships and avoid ambiguity.
Disable Cross-Filter Direction:
If combining bridge tables is not feasible, you can disable the cross-filter direction for one of the relationships. This will prevent Power BI from creating multiple paths.
Create a Combined Key:
Create a combined key in your tables that includes both PI and Sprint. This way, you can use a single bridge table with a composite key.
Example of Using a Single Bridge Table
Create a Combined Key:
In table1 and table2, create a new column that combines PI and Sprint values.
CombinedKey = 'table1'[PI] & "-" & 'table1'[Sprint]

Create a Single Bridge Table:
Create a bridge table that includes the combined key.
BridgeTable = DISTINCT(
UNION(
SELECTCOLUMNS('table1', "CombinedKey", 'table1'[CombinedKey]),
SELECTCOLUMNS('table2', "CombinedKey", 'table2'[CombinedKey])
)
)

Establish Relationships:
Create relationships between the combined key in BridgeTable and the combined key in table1 and table2.

if this does not work, might the PBIX to review

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[EaglesTony]

Not sure if this will work, as I have a slicer based off Sprint and the other based off PI and I need it both directions, so when I select either a Sprint or PI, it will filter my data correctly in both tables.

I'm wondering if I do what you say and put a combined key on the 1 bridge table, but also keep those 2 fields (Sprint and PI) seperate on the bridge table will solve my issue.

[saud968]

You can try it if it helps then good, otherwise we have other expert can review this further. 

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[EaglesTony]

I will try it and if it works, will post the solution here for others.

[Anonymous]

Hi, @EaglesTony 
Thank you for your swift response. I look forward to your updates and hope that our assistance proves effective for you.

Additionally, if you find our help beneficial, I would greatly appreciate it if you could accept saud968's response as the solution. This would significantly contribute to the growth of our community.

Thank you in advance for your consideration. Wishing you a wonderful day ahead!

 
Of course, if you have any new ideas, you are welcome to contact us.
 

Best Regards,

Leroy Lu