Relationship Inactive Connection

[kfitzek]

Hello all,

I'm working on getting three tables to talk to each other and I'm running into some issues with them being ambigous on the connections. I need to have the date fields (year) talk to each other between the two tables below that show the inactive relationship. They are already connected on a part level basis and it will only let me make one connection between the two tables without getting an error. I tried creating an intermediate date table to have them pass through that as a work around and ended up with the same error of the paths being ambigous between the two.

[DataNinja777]

Hi @kfitzek ,

You're running into an ambiguous relationship issue because Power BI only allows one active relationship between two tables, and right now, RM Global Summary and secure_prh_summary are already connected via Item Number. You tried connecting them also by Year, but that relationship remains inactive because activating both would confuse the model—Power BI wouldn’t know which one to use when filtering. Adding a Date table sounds like a good idea, but it didn’t help because now Power BI has even more paths to choose from, especially since the tables are still directly connected on part numbers. The cleanest solution is to leave the Year relationship inactive and only activate it within a specific measure using the USERELATIONSHIP function. For example, if you're trying to calculate sales filtered by year, your measure should look like this:

Sales by Year = 
CALCULATE(
    [Total Sales],
    USERELATIONSHIP('RM Global Summary'[Year], 'secure_prh_summary'[Formatted Year])
)

This tells Power BI exactly which relationship to use, avoiding the ambiguity. Trying to force multiple active relationships between the same tables or bridging through a Date table while a direct connection still exists will just keep causing conflicts. Keep the direct relationship for parts active, and activate the year-based one only when needed inside your DAX.

Best regards,

[kfitzek]

This might work... is there a way to get my [total sales] into the formula without converting it to a measure? As of right now it's pulling a fixed column that comes from the data source so it won't let me add the field to the formula.

[speedramps · Accepted solution]

You need to learn data modelling skills and how to build stare schema,

rather than simply try run report directly off your sourre data.

Learn how here

https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

https://www.youtube.com/watch?v=vZndrBBPiQc

You are trying to build a part number and date relationshio between 2 tables.

This strongly indicates that you are ignoring data modell star schema concepts

Redesign the model like this.