Chapter 12 · 8 levels
Optimising Power BI reports
Find out why a report is slow and fix it: the storage and formula engines, measures that let CALCULATE do the work, column types and cardinality, lean Power Query imports, storage modes, relationships without traps, and VAR.
StartA report that takes eight seconds to react to a slicer doesn’t get used, however good its numbers are. When Harbor & Pine’s managers say “the dashboard is slow”, the cause is almost never the visuals themselves. It is a measure that makes Power BI work row by row, a column that compresses badly, an import that brings in five years of data nobody looks at, or a relationship that sends filters in both directions.
This chapter is about finding the weak link and fixing it. You learn what the two DAX engines do
and how to write measures that keep the fast one busy, why integers compress better than long
text and IDs, how to cut a Power Query import down to what the report needs, when Import,
DirectQuery or a composite model fits, and why single-direction, many-to-one relationships are
both faster and less ambiguous. You also rewrite measures with VAR, so an expression is worked
out once instead of three times.
The DAX levels run in a practice engine on this page that checks every cell of the visual; it
can’t time your measure the way Power BI’s Performance analyzer does, so where speed is the
point, the level checks the pattern too. Every lesson gives the exact place in Power BI Desktop to
measure and fix it for real. The boss level rescues a slow discount report: one measure rewritten,
one built on it with VAR, and a leaner import.
Levels
- The storage engine and the formula engine
from Amanda Price, Head of E-commerce
performance, storage engine, formula engine, vertipaq, performance analyzer
- Fix the slow measure
from Victor Ramirez, Marketing Director
performance, calculate, filter, sumx, storage engine, column filters
- Column types, cardinality and compression
from Olivia Grant, Data Engineer
performance, vertipaq, compression, cardinality, data types, model size
- A lean import that folds
from Eric Sullivan, Operations Manager
power query, query folding, filter rows, choose columns, change type, cardinality
- Import, DirectQuery or a hybrid?
from Hannah Reyes, Chief Financial Officer
storage mode, import, directquery, composite model, hybrid table, dual, live connection
- Take the traps out of the relationships
from Chris Morgan, Merchandising Lead
relationship, cross filter direction, bidirectional, many to many, star schema, performance
- Work it out once with VAR
from Laura Chen, Pricing Analyst
var, return, divide, performance, readability, measure
- Boss: rescue the slow discount report
from Patrick Owens, Chief Operating Officer
performance, calculate, filter, var, divide, power query, query folding, boss