Power BI data modeling 101: star schema, relationships, and why your measures are wrong

If you have built a Power BI report and the totals look wrong, a slicer filters the wrong things, or a measure double-counts, the problem usually is not your formulas. It is your data model. This guide is for the SMB analyst or operations lead who can build a chart but has never deliberately shaped the tables underneath it. By the end, you will know the difference between fact and dimension tables, how to set relationships and cross-filter direction, and how to build and mark a date table so time intelligence works.

Before you start

• Power BI Desktop installed (a recent version from the Microsoft Store or the standalone installer).
• A sample dataset with at least one transactional table (sales, invoices, tickets) and one or more lookup tables (customers, products, dates).
• A basic comfort with the Power BI Desktop layout: the Report view, Table view, and Model view icons down the left edge.

1. Understand fact tables versus dimension tables

A fact table holds the events you measure: one row per sale, per invoice line, per support ticket. It has numeric columns you add up (quantity, amount, hours) and key columns that point at the things involved (a CustomerKey, a ProductKey, a date).

A dimension table describes one of those things: one row per customer, per product, per date. It holds the labels you slice and group by (customer name, region, product category, month).

The shape we are aiming for is a star schema: one fact table in the middle, dimension tables around it, each connected to the fact by a key. Drawn out, it looks like a star.

2. Recognize why one big table breaks totals

Many SMB reports start as a single flat export, often a spreadsheet, where every row repeats the customer name, the region, the product category, and the amount. This is the one-big-table (OBT) pattern, and it causes two specific problems.

First, totals double-count. If the customer's annual revenue is repeated on every order row, summing that column gives you revenue times the number of orders. Second, filtering gets unpredictable. A slicer built on a repeated text column filters rows, not the entities you think you are filtering. The fix is to split the descriptive columns into dimension tables and keep only keys and measures in the fact.

3. Load your tables and open Model view

1. On the Home ribbon, select Get data and load your fact table and each dimension table as separate queries.
2. Select Close & Apply to land them in the model.
3. Select the Model view icon on the left edge. You will see each table as a box, with any auto-detected relationships drawn as lines.

4. Create and manage relationships

Power BI tries to auto-detect relationships, and it is often wrong, so check every one.

1. In Model view, drag the key column from a dimension table (for example, Customer[CustomerKey]) onto the matching key in the fact table (Sales[CustomerKey]). A line appears.
2. To inspect it, on the Home ribbon (or Modeling tab) select Manage relationships. Each relationship shows the two columns, the Cardinality, and the Cross-filter direction.
3. For a clean star, each relationship should be One to many (1:*) from the dimension (the one side) to the fact (the many side).

5. Set cross-filter direction deliberately

Cross-filter direction decides which way a filter flows across a relationship.

• Single means the dimension filters the fact, but not the reverse. This is the default and the right choice for almost every relationship in a star schema. A customer slicer filters sales; that is what you want.
• Both (bidirectional) lets the filter flow in both directions. It is occasionally needed for many-to-many bridge tables, but it is a frequent source of ambiguous filter results, slow reports, and circular dependencies.

Keep cross-filter direction on Single unless you have a specific reason and have tested the result. If a measure starts returning blanks or odd totals after you set a relationship to Both, that is your first suspect.

6. Build a dedicated date table

Time intelligence (year-to-date, same period last year) needs a continuous date table, not the dates scattered in your fact table.

1. On the Modeling tab, select New table.
2. Enter a DAX definition that generates a continuous range, for example:

Date =
CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2026, 12, 31 ) )

3. Add helper columns (Year, Month, Month number, Quarter) with New column, so you can sort and group cleanly.
4. In Model view, relate Date[Date] to your fact table's date key as One to many, Single direction.

7. Mark it as a date table

This is the step people skip, and it is why their time intelligence quietly returns wrong numbers.

1. In the Fields pane, right-click your Date table.
2. Choose Mark as date table, then Mark as date table again.
3. In the dialog, pick the column that holds the continuous dates and confirm. The column must have unique values, no blanks, and no gaps.

8. Verify the model works

1. Drop a simple measure (for example, Total Sales) into a card visual.
2. Add a customer slicer and a date slicer.
3. Confirm the card total matches a number you can verify by hand, and that selecting one customer changes the card.
4. Build a quick table of Sales by Month and confirm the months are in calendar order, not alphabetical. If they are alphabetical, your Month column needs Sort by column set to a month-number column.

What to do next

With a clean star schema and a marked date table, your measures have a solid foundation. The natural next step is writing DAX measures (a SUM, a CALCULATE with a filter, and year-over-year comparisons) on top of this model. If your real data is messier than a tidy sample, or you are not sure whether to split a wide table, that is exactly the kind of modeling decision we help SMBs get right before the report work begins. Let's talk it through.