← Back to Chapters

Power BI Data Modeling – Relationships

? Power BI Data Modeling – Relationships

✨ Quick Overview

Relationships define how tables talk to each other in Power BI. Without correct relationships, filters, aggregations, and DAX calculations will always return incorrect results.

? Key Concepts

  • Relationships connect Fact (transactions) and Dimension (attributes) tables.
  • Cardinality: Defines the nature of the link (usually 1-to-Many).
  • Cross-filter Direction: Determines which way the filter flows (Single is preferred).
  • Correct modeling improves report performance and data accuracy.

? Explanation & Syntax

In Data Modeling, we follow the Star Schema principle where one central Fact table is surrounded by Dimension tables.

Primary Key (Dimension): Unique ID (e.g., CustomerID)
Foreign Key (Fact): Repeated ID used for transactions
Filter Flow: Filters flow from the '1' side to the '*' side.
? View Sample Data (Fact & Dimensions) 
// Fact Sales
SalesID,CustomerID,ProductID,Amount
1,C001,P101,15000
2,C002,P102,45000

// Dim Customer
CustomerID,Name,City
C001,Rahul,Pune
C002,Anita,Mumbai

? Use Cases

  • Sales Analysis: Analyzing revenue filtered by Customer City or Product Brand.
  • Inventory Management: Linking warehouse stock (Fact) to Product details (Dimension).
  • Financial Reporting: Connecting transactions to a central 'Date' table for time-intelligence.
  • Employee Dashboards: Linking sales performance to employee demographics.

⚡ Interactive Relationship Simulator

Select a Dimension Filter to see how it filters the Fact table:

Fact Table View:
[C001 | 15,000]
[C002 | 45,000]
[C001 | 9,000]

? Tips & Best Practices

  • Use Star Schema: Always try to have one Fact table and multiple Dimensions.
  • Avoid Many-to-Many: These can create ambiguity; use a "Bridge Table" if necessary.
  • Single Direction: Keep cross-filter direction as "Single" unless you specifically need bi-directional filtering for a specific DAX requirement.
  • Hide Foreign Keys: Hide the ID columns in the Fact table to prevent users from using them in visuals.

? Try It Yourself

  1. Import the Sample Fact and Customer tables.
  2. Go to the Model View in Power BI.
  3. Drag CustomerID from Dim_Customer to CustomerID in Fact_Sales.
  4. Check that the relationship is 1:Many with a Single filter direction.