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

[BrianLoftonSS]

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.

Screenshot below shows the 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.

How can this be modeled so I have two tables related to facts. And then they have a common rollup? 

Sample Data

Sample Data

Table Diagram

Table Diagram

[v-lionel-msft]

Hi @BrianLoftonSS ,

Has your problem been solved?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lionel-msft]

Hi @BrianLoftonSS ,

Please try this model.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BrianLoftonSS]

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]

@BrianLoftonSS 

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.

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).

[Erokor]

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.

[PaulDBrown]

@Erokor 

I still think it's wiser to create a separate Dim customer table. 
FWIW, I have never used a bi-directional relationship on one-to-many relationships.

In the specific instances where I need a fact table to filter a dim table, I use the function RELATEDTABLE. Here is one example:

https://community.powerbi.com/t5/Desktop/Help-Needed-w-Data-Model/m-p/1655298#M662710 

[Erokor]

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 dimCustomerMeter as as a filter/slicer.

[PaulDBrown]

@BrianLoftonSS 

Can you share a dummy PBIX file or dataset to work on?