Star Schema in Power BI: How to Model Your Data

A star schema is the recommended way to model data in Power BI: one central fact table with the numbers you measure, surrounded by dimension tables that describe them (dates, products, customers, stores). It makes reports faster, DAX simpler and numbers more trustworthy. This guide explains the idea in plain language and shows you how to turn a flat spreadsheet into a star schema step by step.

What is a star schema?

Picture a star. In the middle sits a fact table, such as Sales, with one row per transaction or line item. Each row holds numeric values (amount, quantity, cost) and keys that point to the dimensions. Around it, each dimension table holds one row per thing: one row per product, one per customer, one per day.

Relationships connect each dimension to the fact table, almost always as one-to-many: one product appears in many sales. When you filter the Product table to "Beverages", that filter flows down the relationship to the Sales table, and every measure responds.

Why Power BI loves star schemas

• Performance. The VertiPaq engine compresses repeated values very efficiently. Moving long text columns like product names out of a multi-million-row fact table into a small dimension saves memory and speeds up queries.
• Simpler DAX. Measures like SUM ( Sales[Amount] ) just work because filters flow naturally from dimensions to facts. Many "complicated DAX" problems disappear with a better model.
• Consistent filtering. One Date table can filter Sales, Budget and Inventory at the same time, so you can compare actual vs. budget in a single visual.
• Easier for report users. Field lists are organized: descriptive fields in dimensions, measures in one place.

Microsoft's own Power BI guidance recommends star schema design, and it is a core topic in the PL-300 exam.

Flat table vs. star schema: a quick example

Imagine an export from your sales system with these columns in one big table: OrderID, OrderDate, CustomerID, CustomerName, City, State, ProductID, ProductName, Category, Brand, Quantity, Amount. It works for a quick pivot, but every product name and city repeats on thousands of rows, and adding a second fact table (like a budget) becomes painful.

In a star schema, the same data becomes:

• Sales (fact): OrderID, OrderDate, CustomerID, ProductID, Quantity, Amount
• Customer (dimension): CustomerID, CustomerName, City, State
• Product (dimension): ProductID, ProductName, Category, Brand
• Date (dimension): Date, Year, Quarter, Month, Weekday

How to build a star schema in Power BI, step by step

1. Identify the facts. Ask "what am I measuring?" Usually it is the transactions: sales lines, orders, tickets. Decide the grain, meaning what one row represents (for example, one product on one order).
2. Identify the dimensions. Ask "how do people want to slice it?" By date, product, customer, store, salesperson. Each answer is a candidate dimension.
3. Create dimension queries in Power Query. Right-click your flat query and choose Reference. In the new query, keep only the dimension columns and remove duplicates on the key column.
4. Trim the fact table. In the original query, remove the descriptive columns you moved to dimensions, keeping only keys and numeric columns.
5. Add a date table. Create a continuous calendar and mark it as a date table.
6. Create relationships. In Model view, drag each dimension key onto the matching fact key. Confirm one-to-many with single filter direction.
7. Hide keys and write measures. Hide the key columns in the fact table so users slice by dimension fields, then build explicit measures.

Here is what step 3 looks like in M for the Product dimension:

let
    Source = SalesFlat,
    KeepCols = Table.SelectColumns(Source, {"ProductID", "ProductName", "Category", "Brand"}),
    Product = Table.Distinct(KeepCols, {"ProductID"})
in
    Product

And a few base measures that sit on the fact table:

Total Sales = SUM ( Sales[Amount] )

Units Sold = SUM ( Sales[Quantity] )

Customers = DISTINCTCOUNT ( Sales[CustomerID] )

Avg Order Value = DIVIDE ( [Total Sales], DISTINCTCOUNT ( Sales[OrderID] ) )

Keys, grain and role-playing dimensions

A few modeling details make the difference between a star schema that "mostly works" and one you can trust.

Every dimension needs a unique key

The key column on the "one" side of a relationship must be unique. If two rows in Product share the same ProductID, Power BI will either refuse to create a one-to-many relationship or switch it to many-to-many, and your totals can double count. Removing duplicates in Power Query is the usual fix, but also ask why duplicates exist: sometimes the same product was renamed and the source keeps both versions.

Handle missing keys on purpose

If a sale has a ProductID that does not exist in the Product table, Power BI groups it under a blank row. Instead of hiding the problem, add an "Unknown" row to the dimension or fix the source so those sales are visible and explainable.

One date table, several date columns

A fact table may have an order date, a ship date and a due date. That is a role-playing dimension. Create one active relationship (usually order date) and inactive relationships for the others, then activate them inside measures with USERELATIONSHIP. Alternatively, load a second copy of the date table if users need to slice by both dates at once.

Common star schema mistakes

Snowflaking too much

A snowflake schema splits dimensions further, for example Product to Subcategory to Category, each in its own table. It is valid, but in Power BI it usually adds relationships without much benefit. Flatten those chains into a single Product dimension unless you have a specific reason not to.

Using bidirectional filters to "fix" things

If a visual is not filtering as expected, it is tempting to set relationships to Both. This can create ambiguity and slow performance. Usually the real fix is a missing dimension or a measure that should handle the logic. Read more in our guide to cardinality and filter direction.

Mixing grains in one fact table

Daily sales and monthly budgets should live in separate fact tables, both connected to shared dimensions. Forcing them into one table leads to duplicated or missing numbers.

Keeping descriptive columns in the fact table

If you see names, categories or addresses in the fact table, they probably belong in a dimension.

Next steps

Take one report you already have that is built on a single flat table and rebuild it as a star schema using the seven steps above. Compare the field list, the DAX and the file size before and after. Once your model is clean, time intelligence becomes much easier, so continue with our guide to DAX time intelligence.

Watch: video lessons

▶

▶

Frequently asked questions