Star Schema & Relationships | Guided by A Guide to Cloud

Connecting your tables

Simple explanation

Think of a family tree. Each person has connections — parent to child, sibling to sibling. The connections define how information flows between people.

In Power BI, relationships connect your tables. They define how filters flow from one table to another. When you click “North Region” on a slicer, the filter flows through relationships to filter the fact table — showing only North Region sales amounts in your measures.

Getting relationships right is the difference between a model that “just works” and one that gives wrong numbers.

Cardinality: how rows match

Cardinality defines how many rows on one side match rows on the other side.

One-to-Many is the standard. Many-to-Many should be a last resort.
Cardinality	What It Means	Example
One-to-Many (1:*)	One row in the dimension matches many rows in the fact table. THE standard for star schemas.	One product → many sales transactions
Many-to-One (*:1)	Same as one-to-many but viewed from the other direction.	Many sales transactions → one product
One-to-One (1:1)	Each row matches exactly one row. Rare — usually means tables should be merged.	Employee → Employee Details (same key)
Many-to-Many (:)	Multiple rows match multiple rows. Use carefully — can cause unexpected results.	Students ↔ Courses (students take many courses, courses have many students)

Riley at Coastal Fresh (🛒) has a classic star schema:

Products (1) → Sales (*) — one product appears in many sales
Stores (1) → Sales (*) — one store has many sales
Dates (1) → Sales (*) — one date has many sales

The “one” side has unique values (ProductID, StoreID, DateKey). The “many” side has repeated values (the same ProductID appears in thousands of sales rows).

Cross-filter direction

Cross-filter direction controls which way filters flow through a relationship.

Single direction (default): Filters flow from the “one” side to the “many” side. When you filter Products, Sales gets filtered. But filtering Sales does NOT filter Products.

Both directions (bi-directional): Filters flow both ways. Filtering Sales ALSO filters Products.

Exam tip: avoid bi-directional unless necessary

Bi-directional filtering can cause:

Ambiguous filter paths (circular dependencies)
Performance degradation (more filter calculations)
Unexpected results (filters propagating in directions you didn’t intend)

The exam expects you to use single-direction (dimension → fact) as the default. Only use bi-directional when you genuinely need it — for example, when a bridge table sits between two many-to-many tables.

Role-playing dimensions

A role-playing dimension is a single dimension table used in multiple relationships with different meanings.

Kenji at Apex Manufacturing (🏭) has a Date dimension and his Orders fact table has three date columns:

OrderDate — when the order was placed
ShipDate — when it was shipped
DeliveryDate — when it arrived

Each needs a relationship to the Date table, but Power BI allows only one active relationship between two tables.

Solution:

Create the relationship from Date to OrderDate — make it active
Create relationships from Date to ShipDate and DeliveryDate — make them inactive
Use the USERELATIONSHIP DAX function to activate inactive relationships in measures:

Shipped Orders = 
CALCULATE(
    COUNTROWS(Orders),
    USERELATIONSHIP(Orders[ShipDate], 'Date'[Date])
)

Alternative: separate date tables

Instead of one Date table with inactive relationships, some modellers create separate date tables: OrderDate, ShipDate, DeliveryDate. Each gets its own active relationship. This avoids USERELATIONSHIP but adds table clutter. The exam tests both approaches — know when each is appropriate.

Star schema: the golden pattern

In a well-designed star schema:

Fact table sits in the centre with numeric measures
Dimension tables surround it, each connected by a one-to-many relationship
Filters flow from dimensions → facts (single direction)
No direct relationships between dimension tables

Dr. Ethan Moss at Bayview Medical Centre (🏥) models patient appointments:

[Patients] 1──* [Appointments] *──1 [Doctors]
                      │
                      *──1 [Departments]
                      │
                      *──1 [Dates]

Appointments is the fact table. Patients, Doctors, Departments, and Dates are dimensions. Each dimension filters Appointments independently.

Knowledge check

Question

What cardinality should most relationships in a star schema have?

Click or press Enter to reveal answer

Answer

One-to-Many (1:*). The dimension table is on the '1' side (unique key) and the fact table is on the '*' side (repeated key values).

Click to flip back

Question

What is a role-playing dimension?

Click or press Enter to reveal answer

Answer

A single dimension table (like Date) that relates to the same fact table through multiple relationships — each representing a different role (OrderDate, ShipDate, DeliveryDate). Only one relationship can be active; others use USERELATIONSHIP in DAX.

Click to flip back

Question

What is the default cross-filter direction?

Click or press Enter to reveal answer

Answer

Single direction — filters flow from the 'one' side (dimension) to the 'many' side (fact). This is the recommended default for star schemas.

Click to flip back

Knowledge Check

Kenji has one Date table and an Orders fact table with OrderDate, ShipDate, and DeliveryDate columns. He creates three relationships. What should he do?

Knowledge Check

Riley's star schema has Products → Sales and Stores → Sales relationships. She sets both to bi-directional cross-filtering. What potential problem could this cause?

🎬 Video coming soon

Next up: Date Tables and Table Properties — build a proper date dimension.