Power BI
Intermediate 15 min read Power BI

Data Modeling in Power BI

Relationships decide which numbers your measures can see

A Power BI model says how tables join and how filters flow. Star schemas, single active relationships and correct cardinality keep models fast and totals correct.

star schemarelationshipcardinalityfact tabledimensionmodel viewfilter direction
Prerequisites: DAX and the Measure Context
Before we start

What you will learn

  • Explain the star schema: one fact, several dimensions
  • Construct one-to-many relationships with correct filter direction
  • Recognize why an extra active relationship breaks measures
Introduction

What is this?

Data modeling is choosing tables, relationships and column roles so DAX can compute reliably. The recommended shape is a star: a central fact table holding transactions or events, and small dimension tables (date, customer, product, region) that describe and filter the facts. Relationships define how filters travel from a dimension to the fact, and cardinality defines how many rows match.

Motivation

Why do you need it?

  • A flat mega-table fights you: totals duplicate and every new measure needs hard-wired filters.
  • A star keeps the model narrow, compressed and fast in the in-memory engine.
  • A clean model needs fewer measures because DAX reuses the relationship instead of repeating joins in every formula.
  • Bad relationships are the #1 cause of wrong subtotals and blank measures.
Think about it

Mental model

Imagine

A library catalogue

Think of

A dimensional model

The catalogue lists every book once in a dimensions table keyed by ISBN, while the lending-facts table references ISBNs. To know how many times each author was borrowed you join the loan facts to the author dimension — not store author names in every loan row.

Mechanics

How it works

  • A fact table stores measures (amount, quantity, profit) and foreign keys to dimensions.
  • A dimension table stores attributes at one grain — one row per customer, product, date.
  • A one-to-many relationship filters from the dimension side to the fact side by default.
  • The model engine compresses each column; narrow, high-cardinality columns stay cheap.
  • Cross-filter direction defaults to single, which prevents unwanted fan-out between dimensions.

A star schema: facts in the middle, dimensions around it.

Date dimension
Orders (fact)
Customer dimension
Product dimension
Filters flow
Real world

In production

  • A retail model keeps one Sales fact and Date, Product, Store dimensions; every report slices by store without re-joining.
  • A payments team marks one active relationship to the latest merchant table and 20 hidden ones for audit history.
  • A marketing team models a 'first touch' dimension so DAX can define attribution once instead of per report.
Guidelines

Best practices

  • Name keys consistently (customer_id etc.) and mark them as keys in Power Query.
  • Keep one active relationship per pair of tables; hide alternatives.
  • Set column data types and date keys as whole-number YYYYMMDD for fast filters.
  • Add a hidden 'All' measure note: never block filter propagation with blank calendars.
Watch out

Common mistakes

  • Building many-to-many joins because you skipped modeling; they force ambiguous total logic.
  • Letting relationships point from the fact to multiple dimensions with both directions cross-filtered.
  • Leaving dates as text columns so date-intelligence DAX cannot run.
  • Importing entire tables unmodeled (hours, minutes, seconds) into every model.
Root cause analysis

Common issues

Error
A dimension disappears from filters after a refresh
Root cause

The key column lost its type or new rows have trailing spaces

Fix

Trim and set types in Power Query before import; then re-create the relationship.

Error
A measure returns nothing when sliced by one dimension
Root cause

The relationship points the wrong way or is inactive

Fix

Verify the line arrows point from dimension to fact and activate the intended join.

Error
Dollar totals double when a second dimension is added
Root cause

Two active paths from a dimension cause fan-out through a many-to-many

Fix

Reduce to a single fact-key path and mark extra relationships inactive.

When things break

Debugging tips

  • Visualize the relationship diagram: every line should cross from dimension to fact.
  • Check cardinality by counting distinct keys in each table before joining.
  • Turn on 'Cross filter direction: Both' only when a specific measure needs it, and re-test it.
  • Use the Model view's line list to find inactive relationships quickly.
Interviews

Interview checkpoint

Practice

Hands-on lab

Model a Sales Star
30 min · intermediate
In progress
Objectives
  • Load a fact and three dimensions
  • Wire relationships on clean keys
  • Verify filter flow across dimensions with one measure
  • Repair a broken ambiguous relationship

Model a Sales Star

30 min · intermediate

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which schema shape is recommended for a Power BI model?

2

Every pair of tables may have many active relationships at once.

Go deeper

Official documentation

Model relationships in Power BI
docs
Star schema design (Microsoft)
docs