Forum Discussion

EaglesTony's avatar
EaglesTony
Post Prodigy
1 year ago
Solved

How to fix Circular reference in Data Relationship

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(S...
Need Help
  • EaglesTony's avatar
    EaglesTony
    1 year ago

    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"

saud968's avatar
saud968
Memorable Member
1 year ago

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's avatar
EaglesTony
Post Prodigy
1 year ago

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's avatar
    saud968
    Memorable Member
    1 year ago

    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's avatar
      EaglesTony
      Post Prodigy
      1 year ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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