Star Schema and Relationships in Power BI, Explained
· 7 min read
Power BI relationships connect a column in one table to a matching column in another, so that a filter on one table also filters the other. In a star schema, every dimension table (Products, Customers, Date) has a one-to-many relationship to the fact table (Sales), and filters flow in a single direction: from the dimension to the fact. That setup is the default you should start from.
Products[ProductID] 1 ──── * Sales[ProductID]
Customers[CustomerID] 1 ──── * Sales[CustomerID]
'Date'[Date] 1 ──── * Sales[OrderDate]
Cross filter direction: Single (dimension filters Sales)With these three relationships, you put Products[Category] on a visual, drop a revenue measure next to it, and Power BI splits the revenue by category. No join in the formula. This guide shows what each relationship setting does on a Brightline Supply Co. sample, what breaks when one is missing or set the wrong way, and the "fix" AI assistants suggest that makes numbers wrong.
The sample model
Brightline sells office, kitchen and cleaning supplies to businesses. The sample has four tables:
- Sales (fact): eight order lines with OrderID, OrderDate, CustomerID, ProductID, Quantity, UnitPrice, DiscountPct, Status, Channel.
- Products (dimension): six products with ProductID, ProductName, Category, UnitCost.
- Customers (dimension): four customers with CustomerID, CustomerName, Segment, Region.
- Date (dimension): one row per day of 2025. How to build it is in how to create a date table.
Fact tables hold events and numbers (one row per order line). Dimension tables hold descriptions (one row per product, customer or day). Each dimension key is unique, and it appears many times in the fact table.
What a relationship does in a visual
The revenue measure only counts completed orders. With the Products relationship in place, a table visual with Products[Category] returns:
Completed Revenue =
CALCULATE (
SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] * (1 - Sales[DiscountPct]) ),
Sales[Status] = "completed"
)| Category | Completed Revenue (with relationship) | Completed Revenue (no relationship) |
|---|---|---|
| Coffee & Kitchen | 195.50 | 612.10 |
| Paper & Print | 161.60 | 612.10 |
| Cleaning | 255.00 | 612.10 |
| Total | 612.10 | 612.10 |
The right-hand column is what you see if the relationship is missing (or was deleted). The Category filter stays on Products and never reaches Sales, so every row shows the grand total. The same symptom appears when the relationship exists but is inactive (a dashed line in model view), or connects the wrong columns. If every row of a visual repeats the total, open model view first.
Every dimension works the same way. Region lives on Customers, so Customers[Region] on the rows filters Sales through CustomerID: West 388.10, East 149.00, North 75.00.
For SQL users: a relationship behaves like a LEFT JOIN from the fact table to the dimension that Power BI writes for you every time a visual needs it. See LEFT JOIN in SQL and DAX vs SQL.
Cardinality
Cardinality says how many rows can match on each side. Power BI has four options:
| Cardinality | Meaning | Brightline example |
|---|---|---|
| One-to-many (1:*) | Each key is unique on one side, repeated on the other | Products to Sales |
| Many-to-one (*:1) | The same thing, seen from the other table | Sales to Products |
| One-to-one (1:1) | Unique on both sides | A Customers table and a CustomerContacts table with one row each |
| Many-to-many (:) | Duplicates allowed on both sides | Monthly targets per region joined to Customers by Region |
Power BI Desktop detects the cardinality when you create a relationship. Two things to know:
- If a refresh tries to load a duplicate value into the "one" side column, the whole refresh fails. A duplicate CustomerID in Customers is a data problem to clean, not a reason to switch to many-to-many. Removing duplicates is covered in the Power Query tutorial.
- A one-to-one relationship usually means the two tables could be one table. Merge them in Power Query.
If a Sales row has a key that is not in the dimension (say ProductID 999), Power BI keeps the row and shows its revenue under a blank Category. A "(Blank)" row in a visual means the fact table has keys the dimension does not know about.
Cross filter direction
Cross filter direction says which way filters flow:
- Single: the dimension filters the fact. A Region slicer filters Sales. Sales does not filter Products.
- Both: filters also flow from the fact back to the dimension.
Here is the difference with Customers[Region] on the rows, using two simple measures:
Products in Model = COUNTROWS ( Products )
Products Sold = DISTINCTCOUNT ( Sales[ProductID] )| Region | Products in Model | Products Sold |
|---|---|---|
| West | 6 | 4 |
| East | 6 | 2 |
| North | 6 | 2 |
| Total | 6 | 6 |
With single direction, the Region filter reaches Sales but not Products, so COUNTROWS ( Products ) shows 6 everywhere. Setting the Sales to Products relationship to Both would make it show 4, 2 and 2. But you do not need to change the model for that: DISTINCTCOUNT ( Sales[ProductID] ) gives the same answer from the fact table. If you really need a dimension filtered by the fact in one measure, turn on both directions only inside that measure:
Products Sold (Products table) =
CALCULATE (
COUNTROWS ( Products ),
CROSSFILTER ( Sales[ProductID], Products[ProductID], BOTH )
)Note that West's 4 includes All-Purpose Cleaner 5L, which River Print Co only ordered on a cancelled order. Relationships do not know about business rules. Your measures do.
Power BI data modeling: a checklist
- Split the data into facts and dimensions. One fact table of events, one dimension per thing you filter by. A single wide table works for a quick look but gets slow and confusing as it grows.
- Give every dimension a unique key and make sure the fact table uses the same data type.
- Create one-to-many relationships with single direction, dimension to fact.
- Add a proper date table and relate it to the fact's date column, not the auto date hierarchy.
- Keep one active relationship between two tables. For a second date (ship date), add an inactive relationship and activate it per measure with USERELATIONSHIP. See RELATED and USERELATIONSHIP.
- Hide key columns in report view so users slice by names, not IDs.
- Test with a visual you can check by hand, such as revenue by category, before you build the report.
Common mistakes
An AI assistant sets the relationship to Both "so the slicer works"
When a slicer does not filter a visual, AI assistants often suggest changing the relationship's cross filter direction to Both, or setting every relationship in the model to Both. The visual starts reacting, so it looks fixed. The cost shows up later: with Both on several relationships, a filter can reach a table by more than one path, Power BI may refuse to create a relationship because of ambiguity, and numbers on other pages can change in ways nobody expected. Slicers start filtering each other through the fact table too: pick West in a Region slicer and a Product slicer hides every product West did not order.
How to spot it: in model view, a relationship with a double arrow. Ask what question the visual is trying to answer, and solve it in the measure (DISTINCTCOUNT on the fact table, or CROSSFILTER in one measure) instead of in the model.
Many-to-many to get around duplicate keys
"Relationship cannot be created because the column contains duplicate values" is a common error. A typical AI answer is to set the cardinality to many-to-many. That hides the duplicate instead of fixing it. If Customers has 1002 twice, once as "Lakeside Studios" and once as "Lakeside Studios Ltd", a visual by CustomerName shows 149.00 under both names, while the total counts it once. The rows now add up to more than the total. Find and remove the duplicate first.
Relating two fact tables directly
Joining Sales to a Targets table on Region with a many-to-many relationship is fragile. Relate both facts to a shared dimension (a Regions table) instead, and compare them in a measure.
FAQ
What is a star schema in Power BI?
A model with one fact table in the middle and dimension tables around it, each joined one-to-many to the fact. It keeps DAX simple and slicers predictable. A snowflake schema splits dimensions further (Products to Categories), which works but adds relationships to follow.
What are the types of relationships in Power BI?
By cardinality: one-to-many, many-to-one, one-to-one and many-to-many. By direction: single or both. Each relationship is also active or inactive.
Should I use single or both cross filter direction?
Single, in almost every case. Use both only when you have a specific need, such as some many-to-many bridge tables, and prefer CROSSFILTER inside a measure over changing the model.
Why does my visual show the same number on every row?
The column on the rows is not connected to the fact table: the relationship is missing, inactive, or on the wrong columns. It can also be a measure that removes filters, as in ALL and ALLSELECTED.
Do Power BI relationships work like SQL joins?
Similar, but they run automatically. A one-to-many relationship behaves like a left join from the fact to the dimension, and the filter direction decides which table's filters reach the other.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- Connect order lines to products
- How many customers each region has
- Audit: every region sold the same?Audit