Many Power BI problems, from slow reports to measures returning strange totals, come from the data model rather than the DAX. A star schema is the standard way to model data for reporting, and it is simpler than it sounds.
Facts and dimensions
Fact tables record events and their numbers:
- Sales lines, claims, payments, website sessions.
- Many rows, mostly IDs, dates and numeric values.
Dimension tables describe the things involved in those events:
- Product, Customer, Store, Employee, Date.
- Fewer rows, each row unique, with descriptive columns used for slicing and grouping.
The star
Put the fact table in the middle and connect each dimension to it with a one-to-many relationship: one product, many sales lines. Drawn out, it looks like a star.
For a fictional electronics chain, VoltVista:
- Fact:
Sales(DateKey, ProductKey, StoreKey, CustomerKey, Qty, Revenue) - Dimensions:
Date,Product,Store,Customer
Why it works well
- Filters flow naturally: selecting a category in the Product dimension filters Sales to those products.
- DAX stays simple: measures like
SUM(Sales[Revenue])respond correctly to any slicer. - Performance: the engine handles this shape efficiently.
- Clarity: anyone can see what is a number and what is a description.
Relationship settings
- Cardinality: one-to-many from dimension to fact. If Power BI suggests many-to-many, your "dimension" probably has duplicate keys; fix the data.
- Cross-filter direction: single (dimension filters fact) is the safe default. Bi-directional filtering can cause ambiguous paths and confusing results; use it only for a specific reason.
- Active relationships: only one active relationship can exist between two tables. For a second date (order date and ship date), keep one inactive and activate it inside a measure with
USERELATIONSHIP.
The Date table
Always create a dedicated Date table:
- One row per day, covering the full range of your data with no gaps.
- Columns for Year, Quarter, Month, Month Name, Week, Weekday and any fiscal periods.
- Mark it as the date table (Table tools โ Mark as date table).
- Relate it to every date column in your facts.
Time intelligence functions (YTD, same period last year) depend on it.
Common modelling mistakes
- One giant flat table: works for tiny reports, becomes slow and awkward as it grows.
- Duplicate keys in dimensions: causes many-to-many relationships and wrong totals.
- Snowflakes everywhere: dimensions split into chains of tables (Product โ Subcategory โ Category). Flattening them into one Product table is usually better for Power BI.
- Calculated columns for everything: prefer measures for aggregations.
- Hidden text keys with mismatched types: a key that is text in one table and a number in another will not relate.
Check your model
- Does every dimension key column contain unique values?
- Do slicers on each dimension filter the fact as expected?
- Do totals in a simple table visual match a known figure?
Practise
The motor claims Power BI project asks you to model several related tables, which is ideal practice for this. The ideas are the same as keys in SQL; see primary vs foreign keys.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: