Star Schema & Relationships
Design effective data models in Power BI — define relationships, set cardinality and cross-filter direction, and implement role-playing dimensions.
Connecting your tables
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.
| 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 placedShipDate— when it was shippedDeliveryDate— 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
ShipDateandDeliveryDate— make them inactive - Use the
USERELATIONSHIPDAX 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
Kenji has one Date table and an Orders fact table with OrderDate, ShipDate, and DeliveryDate columns. He creates three relationships. What should he do?
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?
Next up: Date Tables and Table Properties — build a proper date dimension.