Forum Discussion
Ambiguity Error Managing Relationships in data model
I’m looking for recommendations on how to best shape my tables in Power BI to avoid many-to-many relationships and ambiguous paths in the data model.
Right now I have two core tables and I’m trying to rethink their structure.
Table 1: Customers
- customer_id – customer_id (key)
- is_new_customer_sales – is_new_customer_sales
- is_returning_customer_sales – is_returning_customer_sales
- entry_date – entry_date / contract_start_date
- exit_date – exit_date / contract_end_date
- product_count – product_count
- last_contract_expiry_date – last_contract_expiry_date
- birth_date – birth_date
- valid_from – valid_from
- valid_to – valid_to
- person_number – person_number / internal_id
- portfolio_from – portfolio_from / coverage_from
- portfolio_to – portfolio_to / coverage_to
- customer_id – customer_id
- contract_management_id – contract_management_id
- product_hierarchy_id – product_hierarchy_id
- customer_segment_mm_id – customer_segment_mm_id
- advisor_id – advisor_id
- portfolio_premium_amount – portfolio_premium_amount
Questions:
- How would you model these tables to avoid many-to-many relationships?
- Would you treat the Customers table as a slowly changing dimension (using valid_from / valid_to and if so, what’s the best way to relate it to the PortfolioFacts table?
- Are there recommended patterns in Fabric / Power BI (bridge tables, role-playing dimensions, date dimensions, etc.) that work best for this sort of customer–contract–product model?
Thanks in advance!
Hi Emma_ ,
Thank you Kedar_Pande , trivedisunita for your inputs.If you get a chance, please review the response shared abiiove and let us know if it aligns with your expectations. Should you need any additional details or clarification, feel free to let us know.
Regards,
Chaithra E.
4 Replies
Hi Emma_ ,
To avoid many-to-many relationships in your Power BI model, you should restructure into a clean star schema: treat PortfolioFacts as the fact table, connect it to dimension tables (Customer, Product, Advisor, Date, Segment), and use bridge tables where multiple keys overlap. The Customers table can act as a slowly changing dimension (SCD) with valid_from/valid_to, but it should not directly create ambiguous paths—use surrogate keys or bridge tables to manage history.
Recommendation Pattern in PowerBI/Fabric:-
Star Schema: Always aim for fact in center, dimensions around.
Role-Playing Date Dimensions: Use multiple relationships from Date to PortfolioFacts (coverage_from, coverage_to, contract_start, contract_end).
Bridge Tables for Many-to-Many: Cleanest way to resolve ambiguous paths.
Inactive Relationships + USERELATIONSHIP: Keep only one active relationship per dimension, activate others in DAX when needed.
Service Accounts for Connections: In Fabric, use service accounts for pipeline/dataflow connections to avoid dependency on individual users (ties back to your earlier consultant issue).
Avoiding Many-to-Many Relationships
Bridge Tables:Example: If customers can belong to multiple segments, create a CustomerSegmentBridge with unique combinations of customer_id and segment_id.
Connect bridge - Customers and bridge → Segments.
Z-Items Approach (Power Query):
Create a unified “entity list” table (e.g., all unique products or contracts) and connect both fact tables to it. This eliminates direct many-to-many joins.
One-Way Filters:
Use single-direction relationships unless bidirectional filtering is absolutely necessary.
Redesign Customers as SCD2 with surrogate keys.
Use PortfolioFacts as the single fact table.
Introduce bridge tables for segments and multi-valued attributes.
Apply role-playing date dimensions for contract/portfolio dates.
Keep relationships single-direction and activate alternates in DAX when needed
If this reply helped you, please mark it as a solution. a kudos mean a lot! It encourages contributors and keeps the community strong.
Thank You
Sunita
- thanks for your input. Does anyone have experience with doing this in Databricks?
My goal would be to reshape the table and create a daily aggregation, for example with columns like:- snapshot_date
- customer_segment
- shop_number
- total_customers
- new_customers_entries (number of new customer entries)
- new_customers_exits (number of new customer exits)
- returning_customers_entries (number of returning customer entries)
Can someone assess how feasible this is ? Customers is acting as both a dimension and a fact, that's the ambiguity.
Make PortfolioFacts your only fact table. Build a clean Customer dimension (one row per customer_id) and connect every dimension to PortfolioFacts only, never dimension to dimension. That removes the many-to-many and ambiguous paths.
Go SCD Type 2 only if you need history, and if so relate the fact on a surrogate key (customer_id + valid_from), not customer_id. Use a role-playing Date dimension with USERELATIONSHIP for your multiple date columns. No bridge tables needed.
Hi Emma_ ,
Thank you Kedar_Pande , trivedisunita for your inputs.If you get a chance, please review the response shared abiiove and let us know if it aligns with your expectations. Should you need any additional details or clarification, feel free to let us know.
Regards,
Chaithra E.
