Power BI data model design relationships - direct active relationship would introduce ambiguity

Question

Can anyone offer some modeling &amp; relationship advise in Power BI?&nbsp;I have two Customer tables at different grains that I am trying to relate to a Customer rollup group (`'dimCustomers'`).&nbsp;The two customers tables (`'dimBillTierCustomer'` and `'dimCustomerMeter'`) are individually related to my fact table (`'factSummaryTicket'`). These two relationships work individually, but I want them to be aware of the relationship they each have to 'dimCustomers', so I can use Customers to filter both tables in the report.&nbsp;When I relate each of them, I get an error message on the second relationship.&nbsp;&gt; You can’t create a direct active relationship between 'dimCustomerMeter' and 'dimCustomers' because that would introduce ambiguity between the tables 'dimCustomers' and 'factSummaryTicket'. To make this relationship active, deactivate or delete one of the relationships between 'dimCustomers' and 'factSummaryTicket' first.
&nbsp;Screenshot below shows the table relations and the error message.&nbsp;Bill Tier is for Customer pricing rules. Customer Meter is customer locations hierarchy. Customer should filter both of these tables.&nbsp;How can this be modeled so I have two tables related to facts. And then they have a common rollup?&nbsp;&nbsp;Sample DataSample Data&nbsp;Table DiagramTable Diagram

pauldbrown · Answer

BrianLoftonSS&nbsp;
Can you share a dummy PBIX file or dataset to work on?

erokor · Answer

Get rid of the relationship from dimCustomerMeter to factSummaryTicket. eticketOperatorID in dimCustomerMeter to dimCustomers can then be made, but make it bi-directional if you plan on using anything in&nbsp;dimCustomerMeter as as a filter/slicer.

brianloftonss · Answer

One simple change gave me the functionality I needed. Without having to relate ‘dimCustomerMeter’ and ‘dimBillTierCustomer’. The solution was to enable ‘Bi-Directional’ instead of ‘Single’.

Bi Directional Relationship

pauldbrown · Answer

BrianLoftonSS&nbsp;
Do be careful with bi-directional filters on one-to-many relationships. (It can wreak havoc in your expected results). They are generally to be avoided unless you know very well what you are doing and control the way filters propagate around the different tables.
&nbsp;
What I would suggest, is to make the customer table a separate Dimension table to both your fact tables with single-to-many relationships.
There are ways to filter the (what you have now as intemediate dim tables) "customer meter" and "bill tier custom" tables based on the selection made in a separate dim customer table. (for example, by applying a measure as a filter in the slicer).
&nbsp;

erokor · Answer

Much agreed on this one Paul - I simply noticed a quick win and attributed it as such since Brian was already stating he was using the dim table for slicing.

Forum Discussion

Power BI data model design relationships - direct active relationship would introduce ambiguity

8 Replies

Related content

Relationship

Relationships

Relationship Issue

Creating relationships

PowerBI Relationships