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

Question

Can anyone offer some modeling & relationship advise in Power BI?

I have two Customer tables at different grains that I am trying to relate to a Customer rollup group ('dimCustomers').

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.

When I relate each of them, I get an error message on the second relationship.

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.

Screenshots below show sample data, table relations, and the error message.

Bill Tier is for Customer pricing rules. Customer Meter is customer locations hierarchy. Customer should filter both of these tables.

Table Relations

+------------------------------------------------------------------------------------------+----------------+-----------------+---------------+-----------+
| Relation   (From : To)                                                                   | CrossFiltering | FromCardinality | ToCardinality | IsActive  |
+------------------------------------------------------------------------------------------+----------------+-----------------+---------------+-----------+
| [gopherMeterId].[factSummaryTicket]     ==> : <==    [bisonMeterId].[dimCustomerMeter]   | OneDirection   | Many            | One           | TRUE      |
+------------------------------------------------------------------------------------------+----------------+-----------------+---------------+-----------+
| [CustBillTierKey].[factSummaryTicket]   ==> : <==    [CustTierKey].[dimBillTierCustomer] | OneDirection   | Many            | One           | TRUE      |
+------------------------------------------------------------------------------------------+----------------+-----------------+---------------+-----------+
| [eticketOperatorId].[dimCustomerMeter]  ==> : <==    [eticketOperatorId].[dimCustomers]  | OneDirection   | Many            | One           | **FALSE** |
+------------------------------------------------------------------------------------------+----------------+-----------------+---------------+-----------+
| [CustKey].[dimBillTierCustomer]         ==> : <==    [eticketOperatorId].[dimCustomers]  | OneDirection   | Many            | One           | TRUE      |
+------------------------------------------------------------------------------------------+----------------+-----------------+---------------+-----------+

Sample Data

Table Diagram

Difficult to say without knowing the meaning of data. Why do you need a relationship between factSummaryTicket and dimCustomerMeter? Is there a way to merge dimBillTierCustomer and dimCustomerMeter into one table? — W.B.
I don't think there is a way to merge dimBillTierCustomer and dimCustomerMeter into one table. The sample data screenshot shows how there are multiple records for one customer in each of the tables. — SherlockSpreadsheets
Ok, and the first question - why do you need a relationship between factSummaryTicket and dimCustomerMeter? Which fields is the relation based on? Do you have meter information in factSummaryTicket? — W.B.
I want the tables related so I can use the dimCustomer.Customer filter in the report and it will filter both tables. The field relationships I have right now are listed in the op. Yes, Meter is used in the join criteria to the factSummaryTicket. — SherlockSpreadsheets

SherlockSpreadsheets SherlockSpreadsheets · Accepted Answer · 2021-02-09T21:28:47

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

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

1 Answers