Bi-directional Relationship Guidance in Power BI

Introduction

• Target Audience: Data modelers using Power BI Desktop.
• Purpose: Guidance on when to create bi-directional model relationships.
• Definition: Bi-directional relationship filters in both directions.

General Recommendations

• Minimize the use of bi-directional relationships.
• Impact:
  • Can negatively affect query performance.
  • May create confusing report experiences.

When to Use Bi-directional Filtering

1. Special Model Relationships
2. Slicer Options "with Data"
3. Dimension-to-Dimension Analysis

Special Model Relationship Types

• One-to-one Relationships:
  • Must be bi-directional.
  • Generally not recommended.
  • Alternatives are discussed in the One-to-one relationship guidance.
• Many-to-many Relationships:
  • Involves dimension tables and bridging tables.
  • Bi-directional filter ensures filter propagation.
  • See Many-to-many relationship guidance for more details.

Slicer Options with Data

• Limits options to where data exists, similar to Excel PivotTables.
• Example model with three tables: Customer, Product, and Sales.
• Modified Model: Shows how bi-directional filtering can limit slicer options to relevant data.
• Considerations:
  • Design may confuse users.
  • Avoid bi-directional relationships due to performance issues.
  • Recommendation: Use visual-level filters instead of bi-directional filters.

Dimension-to-Dimension Analysis

• Treat fact tables as bridging tables to support cross-dimension analysis.
• Example questions: "How many colors were sold to Australian customers?"
• Filters propagate via the fact table using functions like DISTINCTCOUNT.
• Recommendation: Use CROSSFILTER DAX function to activate bi-directional filtering in measure definitions.
• Example Measure: Different Countries Sold = CALCULATE( DISTINCTCOUNT(Customer[Country-Region]), CROSSFILTER( Customer[CustomerCode], Sales[CustomerCode], BOTH ) )
  • Filters in both directions during evaluation.

Additional Resources

• Model relationships in Power BI Desktop
• Understand star schema and its importance
• One-to-one and Many-to-many relationship guidance
• Relationship troubleshooting guidance
• Engage with the Fabric Community for questions and ideas

Conclusion

• Use bi-directional relationships selectively and with an understanding of the trade-offs.
• Consider alternative solutions and design patterns to optimize performance and user experience.