Power BI Data Modeling Basics (Star Schema)

Most Power BI reports that feel slow, return wrong totals, or break the moment you add a slicer share one root cause: a bad data model. The shape of your tables and how they connect decides whether everything downstream is easy or a constant fight. This guide covers the layout Power BI is actually built for — the star schema — in plain, practical terms.

What a data model actually is

A data model is the set of tables you load into Power BI plus the relationships between them. When you open Power BI Desktop (the free authoring app) and load a single flat spreadsheet, you technically have a model — just a one-table one. That works for a quick chart, but it falls apart the moment you want to compare this year to last year, filter by region and product at once, or reuse the same numbers across many visuals.

The fix is to split your data into two kinds of tables that play different roles:

• Fact tables hold the events you measure — one row per transaction, order line, support ticket, or session. They are tall and numeric.
• Dimension tables hold the things you slice and group by — dates, products, customers, regions, employees. They are short and descriptive, with one row per unique item.

Put one fact table in the center with dimensions connected around it and you have a star schema. It is named for the shape: the fact is the hub, the dimensions are the points.

Fact vs dimension: how to tell them apart

The fastest way to classify a column or table is to ask what you do with it. Do you add it up, or do you slice by it?

Aspect	Fact table	Dimension table
Grain	One row per event (a sale, a click)	One row per thing (a product, a day)
Typical columns	Quantity, Amount, Cost, plus key columns	Name, Category, Region, attributes
Size	Many rows (thousands to millions)	Few rows
You typically	SUM, COUNT, AVERAGE these	Filter, group, and label by these
Example	Sales, Orders, Sessions	Date, Product, Customer, Store

A common mistake is stuffing descriptive text into the fact table — repeating the product name and category on every sales row. That bloats the file and makes filtering inconsistent. Instead, keep a ProductKey on the fact and put the name, category, and brand once in a Product dimension. Power Query (the data-prep layer in Power BI) is where you do this reshaping: split columns out, remove duplicates to build a clean dimension, and keep only keys on the fact.

Why a star schema, not one big table

You can mash everything into one wide table, and beginners often do. The star schema wins for concrete reasons:

• Smaller, faster model. Power BI's storage engine (VertiPaq) compresses repeated values well, but storing the same product name on a million rows is still wasteful. Integer keys compress far better than text.
• Predictable filtering. With dimensions on the "one" side, a slicer on Product[Category] cleanly filters the fact. In a flat table or a tangled web, filter direction becomes ambiguous and totals go wrong.
• Reusable dimensions. One Date table can filter Sales, Returns, and Budget at the same time. A flat table cannot do that.
• Simpler DAX. Measures written against a star schema are short and behave the way you expect.

Snowflake schemas (where dimensions branch into sub-dimensions) and other shapes exist, but for the vast majority of business reporting, a clean star is the right answer. Do not over-engineer it.

Relationships and cardinality

A relationship links a column in one table to a column in another — almost always a key column. The two settings that matter most are cardinality and cross-filter direction.

Cardinality describes how rows on each side match up:

• One-to-many (1:*) — the default and the one you want. One row in the dimension matches many rows in the fact. One customer, many orders. One date, many sales.
• Many-to-one (*:1) — the same relationship viewed from the fact side.
• One-to-one (1:1) — rare; usually a sign two tables should be merged.
• Many-to-many (*:*) — powerful but a frequent source of confusing results. Avoid it until you know exactly why you need it.

In a healthy star schema, every relationship is one-to-many running from a dimension to the fact.

One-to-many filtering: the rule that makes it click

Here is the single most important behavior to internalize. By default, filters flow from the "one" side to the "many" side — from the dimension down into the fact — and not back up.

Date (1) ─────► Sales (many)
Product (1) ──► Sales (many)
Customer (1) ─► Sales (many)

Pick "March 2026" on Date   → filters Sales to March
Pick "Footwear" on Product  → filters Sales to footwear
The two filters combine      → footwear sold in March

This is why slicers "just work" in a star schema: each dimension narrows the fact, and multiple dimensions stack together. Filters do not flow the other way by default — selecting a row in Sales will not filter Product — which is exactly what you want, because it keeps behavior unambiguous.

In the relationship editor you will see a cross-filter direction set to "Single" (the default) or "Both." Leave it on Single. Turning on bidirectional filtering everywhere is one of the most common ways people create slow, wrong reports. Only reach for "Both" in specific, deliberate cases.

Always add a dedicated date table

If you do nothing else from this guide, do this. Create one date table with a continuous, gap-free row for every day in your range, then relate it to the date column in your fact. Power BI's classic time-intelligence functions — year-to-date, prior year, rolling averages — depend on a proper, marked date table to calculate correctly.

You can build one quickly in DAX:

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2026, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Month Number", MONTH ( [Date] ),
    "Quarter", "Q" & QUARTER ( [Date] ),
    "Weekday", FORMAT ( [Date], "ddd" )
)

Two follow-ups that people forget:

• Mark it as a date table. Right-click the table, choose Mark as date table, and pick the Date column. This is required for the classic time-intelligence functions to behave correctly.
• Sort Month by Month Number. Otherwise "Apr, Aug, Dec…" sort alphabetically. Select the Month column, then set its Sort by column property to Month Number.

With that in place, a year-to-date measure is trivial:

Sales YTD = TOTALYTD ( SUM ( Sales[Amount] ), 'Date'[Date] )

A worked example you can picture

Say you run an online store and you have one messy export of order lines. Reshape it into a star:

• Sales (fact): OrderDate, ProductKey, CustomerKey, Quantity, Amount. One row per line item.
• Date (dimension): one row per day, marked as a date table, related to Sales[OrderDate].
• Product (dimension): ProductKey, ProductName, Category, Brand. Related to Sales[ProductKey].
• Customer (dimension): CustomerKey, CustomerName, Region, Segment. Related to Sales[CustomerKey].

Now a single measure, Total Sales = SUM ( Sales[Amount] ), responds correctly to any slicer — category, region, month, customer segment — with no extra work. That is the payoff of getting the model right first.

A few habits keep models clean as they grow: hide key columns from report view so users slice by friendly names, hide the raw fact columns and expose measures instead, and give tables and columns readable names in Power Query before anyone builds visuals. None of this is glamorous, but it is the difference between a report you trust and one you constantly second-guess. For more on the calculation layer, see our guide to DAX measures vs calculated columns. And if you are polishing the visuals next, a consistent palette helps — our free Power BI theme generator creates a theme JSON you can drop straight into Desktop.

Send us your data, get a finished model back

Building a clean star schema, a marked date table, and the right relationships takes time and a feel for Power BI's quirks. If you would rather skip the modeling and get straight to insights, that is what Instant PowerBI is built for: send your raw CSV or Excel files and get back a finished, branded report — modeled with fact and dimension tables, a real date table, and working measures — that opens in free Power BI Desktop with the data already embedded. Get started and begin from a working report instead of a blank canvas.