Data Modeling and Relationships: The Star Schema | Free Power BI Course: From Beginner to Advanced

What you'll learn

Why Power BI works better with several related tables than with one giant table.
How to design a star schema with fact and dimension tables.
How to create relationships and what cardinality and cross-filter direction mean.
How to diagnose the classic problems: "(Blank)" rows, repeated totals and ambiguous relationships.
How to find the 3 sales with product code P99 in the Summit file and measure their impact.

📥 Practice this lesson

The Sales table includes 3 lines with product code P99, which doesn't exist. Track them down with what you learn here.

summit-sales-power-bi.xlsx · five tables ready for a star schema

The data model is the invisible part of a report and the part that most determines whether it works. A good model keeps DAX measures simple, makes filters behave the way you expect and keeps the report fast. A bad model produces numbers that don't add up and formulas nobody can write. This lesson opens the Intermediate level with the skill that separates a Power BI user from a Power BI professional.

3 + 2three dimensions (Products, Customers, SalesReps) and two fact tables (Sales and Targets) in the Summit file

13 ≠ 12distinct product codes in Sales vs. 12 in Products: there's an intruder

$508.01in sales lands in "(Blank)" because of that nonexistent code

Why not one giant table?

In Excel it's common to keep everything on one sheet: every sale repeats the customer's name, city and region, plus the product's name, category and brand. With 2 million sales, "Main Street Convenience" and its city repeat thousands of times. That causes problems:

Size and speed: repeated data eats memory.
Inconsistency: if a customer moves, do you update thousands of rows?
Mixing grains is impossible: sales (by day and product) and targets (by month and rep) can't live in the same table.

The star schema

The professional solution is to split data into two kinds of tables:

FactsWhat you measure

Contains: The events you measure: numbers you add or count.
Examples: Sales, Payments, Targets, Inventory.
Shape: Many rows, few columns (keys and numbers).
At Summit: Sales (4,430 rows) and Targets (8 rows, 96 after unpivoting).

Goes in the center

DimensionsWhat you filter by

Contains: The attributes you filter and group by.
Examples: Products, Customers, SalesReps, Calendar.
Shape: Fewer rows, descriptive columns.
At Summit: Products (12), Customers (40) and SalesReps (4).

Goes around it

The fact table sits in the middle with the dimensions around it, like a star:

Sales (facts) 4,430OrderID, OrderDate, CustomerID, ProductID, RepID, Quantity, UnitPrice.
Products 12ProductID, Product, Category, Brand, UnitCost, ListPrice.
Customers 40CustomerID, Customer, City, State, Region, Channel.
SalesReps 4RepID, SalesRep, Region, Email.
Calendar 730One row per day in 2025 and 2026; you'll build it in Lesson 8.

Each relationship runs from 1 (the dimension, where the key is unique) to * (Sales, where the key repeats). The number on the right is each table's row count.

Each dimension has a unique key (ProductID, CustomerID…) and the fact table carries that same key on every row. That way a product's category is stored once and "reached" through the relationship.

1. Which table should hold the "Product category" column?

The Products dimension, once per product.
The Sales fact table, repeated on every row.
The Calendar dimension.

Descriptive attributes you group by belong in dimensions; fact tables only store the product key.

Creating relationships

In Model view, drag ProductID from Products onto ProductID in Sales. Power BI creates the relationship and draws a line with two symbols: 1 on the dimension side and * (many) on the fact side. You can also use Home › Manage relationships.

Property	What it means	Recommended setting
Cardinality	How many rows on each side can match	*One to many (1:)**: one product appears in many sales
Cross-filter direction	Which way filters travel	Single: from the dimension to the facts
Active	Whether it's used by default	Yes (only one active path can exist between two tables)

Filter direction is critical: when a user picks "Category = Beverages" in a slicer, the filter flows from Products to Sales and only beverage sales are added up. To filter Products based on Sales you'd need the "Both" direction, which can cause ambiguity and slow reports. Use it only when you know exactly why.

Hide the keys

In Model view, hide the key columns in the fact table (right-click › Hide in report view). Users will always filter with dimension columns, which is the right way.

Why it matters: filters follow relationships

With a star schema you can put Region (from Customers) and Category (from Products) in the same matrix with sales (from Sales) and everything works: each cell filters Customers by region and Products by category, and both filters flow down to Sales.

Tables with different grains

Summit's targets are by rep and month, not by day or product. Don't push them into the Sales table: load them as a second fact table related to SalesReps (by RepID) and to Calendar (by a date in the month, such as the 1st). Then you can show sales and target side by side by rep and by month, because both tables share those dimensions. What you can't do is see targets by product, because targets don't exist at that level.

2. What's the recommended setup for the relationship between Products and Sales?

Many to many, Both directions.
One to many, Single direction from Products to Sales.
One to one.

Each product is unique in Products and appears many times in Sales; filters flow from the dimension to the facts.

Common problems and how to fix them

Symptom	Likely cause	Fix
A "(Blank)" row appears with sales	Some sales use a code that doesn't exist in the dimension (a new product nobody added, a typo, extra spaces)	Find those codes, fix the dimension or clean the key in Power Query
The same total repeats on every row	No relationship between the axis table and the values table, or the filter runs the wrong way	Create the relationship or use the column from the correct dimension
Power BI won't create a one-to-many relationship	The "dimension" has duplicate keys	Remove duplicates in Power Query or find out why they exist
A relationship shows a dashed line	It's inactive (another active path already exists)	Normal with several dates (order date and ship date); activate it in a measure with USERELATIONSHIP (Lesson 11)
Totals differ from the source system	Duplicate fact rows, loaded total rows, hidden filters	Review Power Query (Lesson 2) and the Filters pane

3. Your sales-by-category matrix for Summit shows a "(Blank)" row with $508.01. What does it mean?

Some sales had no amount.
Power BI has a bug.
Some sales use product codes that don't exist in the Products table.

Sales whose code has no match in the dimension are grouped under "(Blank)". You need to fix the dimension or the key.

A real diagnosis: Summit's P99 code

With the model in place, put Category and sales in a matrix: a "(Blank)" row appears with $508.01. To find the source, put OrderID, OrderDate and ProductID from Sales in a table along with Product from Products, and filter to blank products:

Order	Date	Customer	ProductID	Quantity × Price	Amount
SO1048	01/17/2025	Sunrise Market 12	P99	30 × $13.50	$405.00
SO1300	04/10/2025	Main Street Wholesale 31	P99	7 × $3.71	$25.97
SO1554	07/08/2025	Main Street Wholesale 11	P99	18 × $4.28	$77.04
Total					$508.01

That's just 0.1% of sales, but the impact goes beyond the dollars: because P99 has no cost in Products, those sales will show a 100% margin and no category in every analysis (you'll see it in Lessons 13 and 15). What should you do?

Ideally: ask the sales team which product it was and fix the code in the source system. One clue: $13.50 is exactly the list price of the whole milk 4-pack (P05), but a matching price isn't proof.
In the meantime: don't delete the rows, or your total will stop tying out to the system. You can add a row "P99 · Uncataloged" to Products so the sales show up with a name and nobody mistakes them for a Power BI error.
Always: document the difference on the report's notes page.

Common modeling mistakes

Merging every table in Power Query into one flat table.

Keep Sales to keys and numbers, and descriptions in the dimensions.

Setting every relationship to "Both" so things "work".

Single direction from dimension to facts; "Both" only with a specific reason.

Relating Targets directly to Sales by RepID.

Relate both fact tables to the dimensions they share: SalesReps and Calendar.

Using the ProductID column from Sales in visuals.

Use dimension columns and hide the fact-table keys.

Deleting orphan rows (like P99) to make "(Blank)" disappear.

Fix the key or add the code to the dimension; the total must still match the system.

4. You want to compare sales and targets by rep and by month. How do you connect the Targets table to the model?

With a direct relationship between Targets and Sales on RepID.
By copying the target onto every Sales row with Merge Queries.
By relating it to SalesReps (on RepID) and to Calendar (on the month's date).

RepID repeats in both fact tables, so a direct relationship would be many-to-many. Both tables share the SalesReps and Calendar dimensions, and filters reach both through them.

Practice exercise: build your star schema

Create an Excel workbook with four tables:

Sales: Date, CustomerID, ProductID, RepID, Quantity, Amount, Cost (about 50 rows).
Products: ProductID, Product, Category, Brand (8 products).
Customers: CustomerID, Customer, City, Region (10 customers).
SalesReps: RepID, SalesRep (4 reps).

Load them, create the three relationships and confirm they're all 1:* with Single direction.
Hide the keys in Sales.
Build a matrix with Region on rows, Category on columns and Amount as values.
Add a sale with a ProductID that doesn't exist and watch the "(Blank)" row appear. Then fix it.

See solution

To find codes without a match, put the ProductID column from Sales (unhide it temporarily) in a table with Product from Products. Rows with an empty Product are the orphan codes. Another way, in Power Query: merge Sales with Products using the Left Anti join kind (rows only in the first table). The result is exactly the sales whose code isn't in Products. Fix it by adding the missing product to the Products table (or correcting the typo in the source), refresh, and the "(Blank)" row disappears while the total stays the same.

Summary

Split data into fact tables (numbers) and dimension tables (attributes).
A star schema connects each dimension to the facts through a unique key.
Use one-to-many relationships with Single cross-filter direction, from dimension to facts.
Tables at a different grain, like targets, connect through shared dimensions.
"(Blank)" rows, repeated totals and duplicates are fixed in the model or in Power Query.
Never delete orphan rows: fix them at the source or add them to the dimension, like Summit's P99.

Next lesson: DAX for Beginners: Measures and Calculated Columns. You'll start writing DAX: the difference between calculated columns and measures, and your first professional measures.