🔗 Connecting the Dots: Relationships

In the previous chapter, we learned about placing our tables into a Star Schema. But how do we actually connect them? We use Relationships.

A relationship is a line drawn between a column in one table and a matching column in another table (e.g., connecting ProductID in the Products table to ProductID in the Sales table).

🧮 Cardinality

When you connect two tables, Power BI evaluates the uniqueness of the columns and assigns a Cardinality. This defines the nature of the relationship.

1. One-to-Many (1:*) 🌲
   • The gold standard. A single Product ID appears only once in the Products table (the "One" side), but it can appear many times in the Sales table (the "Many" side) because a product can be sold thousands of times.
2. One-to-One (1:1) 1️⃣
   • Both tables have entirely unique IDs. Usually means you should just merge these two tables together into a single table.
3. Many-to-One (*:1) ⏪
   • The exact same as One-to-Many, just drawn in the opposite direction.
4. Many-to-Many (:) 🕸️
   • Danger! Both columns contain duplicate values. Power BI doesn't know which row connects to which row.
   • Solution: Avoid this if possible. If required, you must build a "Bridge Table" containing unique values to sit between them.

🌊 Relationship Behaviour: Filter Flow

When you draw a relationship line in Power BI, you will notice a tiny arrow in the middle of the line. This arrow dictates the Cross-Filter Direction.

Single Cross-Filter Direction (⬇️) The arrow points from the Dimension table (the "One" side) down to the Fact table (the "Many" side).

• If you filter your report to only show "Red" products, that filter flows down the line to the Sales table, instantly calculating the total sales for Red products.
• Best Practice: Always arrange your Dimension tables physically above your Fact tables on the screen so you can visually see the filters flowing downwards like a waterfall!

Bidirectional Filtering (↔️) The arrow points in both directions. Filtering Table A filters Table B, and filtering Table B also filters Table A.

• Warning: While this sounds great, it can cause severe performance issues and "Relationship Ambiguity" (Power BI gets confused about which path a filter should take). Avoid using this unless strictly necessary!

🟢 Active vs 🔴 Inactive Relationships

Sometimes, two tables share multiple matching columns. For example, a Sales table might have an OrderDate and a ShippingDate. You want to connect both of them to your Calendar table.

• Power BI only allows one Active relationship (a solid line) between two tables at a time. This is the default path filters will take.
• Any additional relationships between those tables become Inactive (a dashed line). Filters will ignore this line unless you explicitly wake it up using a DAX formula (specifically, the USERELATIONSHIP() function).

✅ Data Modelling Best Practices

Follow these golden rules to keep your Power BI model healthy and fast:

1. Prefer the Star Schema: Keep it simple. Dimensions surrounding a central Fact table.
2. Separate Fact and Dimension Tables: Never mix descriptive text attributes (like Customer Name) into your massive Fact tables (like Sales). Keep them physically separate.
3. Prefer One-to-Many (1:*) Relationships: They are the fastest and most reliable.
4. Prefer Single-Direction Filters: Let filters flow downstream from Dimensions to Facts.
5. Hide Technical Fields: Right-click and hide Foreign Key columns (like CustomerID) in your Fact table. Force your users to drag the Customer Name from the Dimension table instead!

🏋️ Practice Drill

Let's create a relationship!

Task:

// Try answering these:
1. In the Power BI Desktop **Model View**, if you have two tables that share a common column (like an ID), click and hold the column name in the first table.
2. Drag your mouse over to the matching column name in the second table and let go.
3. Power BI will draw a line between them!
4. Double-click the line to open the **Edit Relationship** menu. Notice the Cardinality and Cross-filter direction options.

← 📐 Data Modelling and Schemas | Next Topic → 📈 Core Data Visualizations