Ambiguous path - Don't understand why

[DouweMeer]

I have a dimension table that attempts to filter two other dimension tables which are linked to a fact table, say, customer and product. Normally, you'd just create a 1 to many cardinality between this dimensional table that filters the other 2 tables, customer and product, and it is fine. Which I have one, and it works fine. Like, per product only 1 customer, and that customer then can't be used for another product.  

Currently, on a new request, it has many-to-many on both sides, thus creates dependency issues. You can now have multiple customer per product or vice versa. This creates a circular dependency due to many to many cardinality. 

To prevent this, I created two bridge tables on both ends of my filter dimension table to filter the other 2 dimension tables customer and product. The true nature of the bridge table is 1 to 1 cardinality to my filter dimension table, but I thought I override this to a 1 to many cardinality. Sadly, I get now an ambiguous path error regardless.

Blue - Dimension tables (say, customer and product)

Green - does work (say per product 1 unique customer)

Yellow - bridge table

Red - filter dimensional table

When I want to establish between red and yellow, I initially see this:

And then I change it to this, but...:

Red table "should" be able to create similar to Green's table a relationship between both blue dimension tables, but it can't, even though the relationships is set to 1-to-many. 

[v-hashadapu]

Hi @DouweMeer , Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.

[v-hashadapu]

Hi @DouweMeer , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.

[v-hashadapu]

Hi @DouweMeer , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[Thomaslleblanc]

You would have to go back to the data engineer of the dimension and fact and separate thise dependency. Even tough more than one customer can sell a product, you can not have the same customer selling the same product on one sales line item. Designing the customer/product dimension would be a way, but you would need a snowflake ProductList table that can filter to one product if the customer is not included.

[DouweMeer]

I'm that engineer. Also, in the picture, blue box on the left would be product, blue box on the right is customer. They individually filter the fact table. Thus, if you'd filter fact table form the product table, it can individually filter the fact table from the customer table. 

The intent on the red table is that for a specific customer-product combination, it can provide, say, the sales value. And then for a focusses list of customer-product combinations. 

Also, perhaps needed additional context, the blue tables are dimensional table for 2 ERPs at the same time. Intent to see the dollar figure for that customer-product combination from both ERP's perspectives.

[v-hashadapu · Accepted solution]

Hi @DouweMeer , Thank you for reaching out to the Microsoft Community Forum.

Instead of separate bridge tables between your red filter and Customer/Product, build a single Customer Product bridge dimension that links both Customer and Product to Fact. Then connect your red filter table to this composite dimension. This removes ambiguity and correctly represents the many-to-many relationship between Customer and Product without circular dependencies.

Model relationships in Power BI Desktop - Power BI | Microsoft Learn

Many-to-many relationships in Power BI Desktop - Power BI | Microsoft Learn