Forum Discussion
Power BI data model design relationships - direct active relationship would introduce ambiguity
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
PaulDBrownCommunity Champion
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
Resolver II
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
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
