Power BI
Advanced 14 min read Power BI

Power BI Performance Tuning

Measure first, tune what hurts, never guess

Fast Power BI comes from a small model, folded queries, the right storage mode and minimal DAX. Measure first with Performance Analyzer and DAX Studio, then tune what actually hurts.

performanceVertiPaqcardinalitystorage modequery foldingperformance analyzerDAX Studio
Prerequisites: Data Modeling in Power BI
Before we start

What you will learn

  • Identify the slowest visual with Performance Analyzer
  • Reduce model size through column and cardinality reduction
  • Explain storage modes and when to use DirectQuery
Introduction

What is this?

Power BI performance depends on how fast the model answers queries. VertiPaq compresses columns in memory at refresh time; DAX Studio shows the queries a visual sends; Performance Analyzer times each visual on the page. Tuning reduces the data the engine has to scan: fewer columns, low cardinalities, folded ingest, correct storage mode, and simpler measures.

Motivation

Why do you need it?

  • Users abandon slow reports; latency above a couple of seconds kills adoption.
  • Model bloat is cheap to prevent at ingest (column pruning) and painful to charge later.
  • Understanding VertiPaq cardinality explains why text-heavy columns blow up model size.
  • Storage-mode mistakes (DirectQuery vs Import) cause daily performance incidents.
Think about it

Mental model

Imagine

A warehouse with heavy shelves

Think of

The VertiPaq model

The warehouse's cost is floor space (imported model size). You cut cost by storing only SKUs you sell (remove unused columns), stacking like items (low cardinality - shared values compress), and delivering fast pallets close to the loading dock (fold queries in-source).

Mechanics

How it works

  • Import mode copies data into in-memory VertiPaq; DirectQuery passes full SQL to the source each query.
  • VertiPaq compresses columns with dictionaries; high-cardinality columns (GUIDs, full names, timestamps) expand size.
  • Performance Analyzer records query elapsed times per visual for a page.
  • DAX Studio (external) inspects the physical queries, expanding row counts and engines.
  • Measure complexity, especially many iterators over big tables, drives per-query CPU.
  • Query folding pushes aggregations into the model, which is good, but folding into the source requires a folding-capable connector.

The performance tuning loop.

Profile
Localize
Root cause
Fix
Re-measure
Hands-on

Examples

Fixing a slow page in three steps

Fixing a slow page in three steps
1. Performance Analyzer: identify the slow visual.
2. DAX Studio: check the query Rows column - is it scanning the full fact?
3. Fix by: pruning columns, adding a Date relationship,
   moving the transform (folding) into the source SQL view.

A disciplined loop that always starts with measurement, then localizes the exact query, then addresses model vs measure vs source causes rather than trial-and-error settings.

Real world

In production

  • A BI engineer removes eleven unused columns from a 40-column import, shrinking the model by 60% and making every refresh faster.
  • A data team moves a legacy date-heavy column to a date refactor with low-cardinality flags, cutting RAM and boosting query speed.
  • A large enterprise splits models: import-mode for the hot metrics and DirectQuery only for the live ledger page that truly needs source freshness.
Guidelines

Best practices

  • Measure before and after every change; keeping the delta is the whole discipline.
  • Import only columns the report needs; hide or exclude the rest.
  • Replace high-cardinality text with coded keys and a lookup table.
  • Keep Date tables small and reference date-intelligence functions against them.
  • Set the Report Theme / reduce custom visuals if visual load time is the bottleneck.
Watch out

Common mistakes

  • Assuming DirectQuery is 'faster' — it trades refresh for query time and usually needs the source tuned.
  • Fixing the report before checking whether the model is the slow part.
  • Blindly adding .pbix compression to a model whose real problem is query folding.
  • Storing full product descriptions in the fact table instead of the dimension.
Root cause analysis

Common issues

Error
One visual takes 10 seconds though the page is fast
Root cause

A heavy DAX iterator over a big table or a broken fold in a DirectQuery model

Fix

Rewrite the measure as set-based, or push the aggregation further into the query.

Error
Refresh takes forever on a small file
Root cause

Power Query steps un-parallelized, or a merge killed folding

Fix

Enable parallel loading, move merges into the source, and confirm folding in the query editor.

Error
Model size exploded overnight
Root cause

A new text or GUID column with high cardinality entered the model

Fix

Find newest columns in VertiPaq Analyzer and prune or code them into keys.

When things break

Debugging tips

  • Run Performance Analyzer on a static test page: click refresh to baseline the page.
  • In DAX Studio, read the 'Time' and 'Rows' columns of the storage-engine queries.
  • Sort columns ascending on import to help VertiPaq dictionary compression.
  • Compare a slow visual against an identical one on a pruned model to isolate model vs engine.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which tool shows the time each visual takes on a Power BI page?

2

The best way to speed a report is to switch every model to DirectQuery.

Go deeper

Official documentation

Performance Analyzer
docs
VertiPaq Analyzer (DAX Studio)
docs