Implement and Manage Semantic Models · Topic 5 of 6

Query, Visual and DAX Performance

Speed up semantic models with Performance analyzer, DAX Studio and VertiPaq Analyzer: lower cardinality, fewer visuals, query reduction and faster DAX.

5 min

In a nutshell

Performance analyzer times each visual; DAX Studio splits storage engine and formula engine time.
VertiPaq Analyzer shows which columns use the most memory; cardinality is the main driver.
Remove unused columns, reduce cardinality, use integer keys and turn off Auto date/time.

What it is

A slow report usually has one of three causes: a model that is larger than it needs to be, report pages that send too many queries, or DAX that makes the engine work row by row.

Optimizing starts with measuring. You find the slow visual, read its query, see where the time goes, and then fix the model, the page or the measure.

How it works

Tools for finding the problem

Tool	What it tells you
Performance analyzer (Power BI Desktop)	Time per visual, split into DAX query, visual display and other; copy each visual's query
DAX query view	Run and edit the copied query against the model
DAX Studio	Server timings split into storage engine and formula engine time, and query plans
VertiPaq Analyzer (in DAX Studio)	Size of each table and column, and column cardinality
Best Practice Analyzer (in Tabular Editor)	Rule-based warnings about model design

The storage engine scans compressed columns, uses many threads and caches results. The formula engine is single-threaded and handles logic that the storage engine cannot. Fast DAX pushes as much work as possible to the storage engine.

Making the model smaller

Model size and column cardinality drive memory and scan time.

Remove columns and rows that no report uses.
Reduce cardinality: split a datetime column into date and time, round decimals, and avoid unique text columns such as GUIDs unless reports need them.
Use integer keys for relationships.
Turn off Auto date/time and use one date table.
Keep a star schema; it usually performs better than one wide flat table or a deep snowflake.
Add aggregation tables for large DirectQuery facts.

Making report pages lighter

Every visual sends at least one query, and every slicer change re-runs them.

Problem	Fix
Too many visuals on one page	Fewer visuals; move detail to drill-through pages or tooltips
Huge tables and matrices	Top N filters, fewer columns, drill-through for detail
Every slicer click runs all queries	Query reduction options: Apply buttons on slicers and the filter pane
Visuals that filter each other unnecessarily	Turn off interactions that are not needed
DirectQuery pages with many visuals	Reduce visuals, use Dual dimensions and aggregations

Writing faster DAX

Slower pattern	Faster pattern	Why
Repeating the same measure in an expression	Store it in a `VAR`	Evaluated once
`FILTER ( Sales, RELATED ( 'Product'[Color] ) = "Red" )`	`KEEPFILTERS ( 'Product'[Color] = "Red" )`	Filters a small column instead of iterating the fact table
`IF ( [Sales] = 0, BLANK (), [Profit] / [Sales] )`	`DIVIDE ( [Profit], [Sales] )`	Simpler and optimized
`IFERROR` and `ISERROR` around calculations	Avoid the error, for example with `DIVIDE`	Error handling forces slower evaluation
`[Total Sales] + 0` to show zeros	Leave the blank	Blanks let the engine skip empty combinations
Nested iterators over large tables	Iterate the smaller table, or precompute a column upstream	Fewer rows in the formula engine

-- slower: iterates every Sales row
Red Sales Slow =
CALCULATE ( [Total Sales], FILTER ( Sales, RELATED ( 'Product'[Color] ) = "Red" ) )

-- faster: filters the Color column
Red Sales =
CALCULATE ( [Total Sales], KEEPFILTERS ( 'Product'[Color] = "Red" ) )

The blank rule matters more than it looks. A measure that turns blank into zero makes a matrix of customers by products show every combination, so a page that showed a few hundred cells now computes millions.

A worked example

A sales overview page takes 12 seconds to load.

Performance analyzer shows a matrix with 9 seconds of DAX query time; the other 20 visuals take under a second each.
The matrix query copied into DAX Studio shows most time in the formula engine. Its margin measure uses FILTER over the Sales table and IFERROR.
Rewriting the measure with variables, DIVIDE and a column filter moves the work to the storage engine.
VertiPaq Analyzer shows an OrderDateTime column with millions of distinct values. Splitting it into a date and a time column shrinks the model.
Turning on the query reduction option that adds an Apply button to slicers stops a query storm while users pick several values.

Exam tip

First decide where the time goes. A slow "visual display" time is a report design problem; a slow "DAX query" time is a model or measure problem.

How the exam asks about this

"Identify which visual on a page is slow." Performance analyzer in Power BI Desktop.
"Reduce the size of a model with a high-cardinality datetime column." Split it into separate date and time columns, or drop the time part if it is not needed.
"Users select several slicer values and each click refreshes all visuals." Enable query reduction with Apply buttons.

What to remember for the exam

Performance analyzer times each visual; DAX Studio splits storage engine and formula engine time.
VertiPaq Analyzer shows which columns use the most memory; cardinality is the main driver.
Remove unused columns, reduce cardinality, use integer keys and turn off Auto date/time.
Fewer visuals, Top N filters and query reduction Apply buttons make pages faster.
Use variables, DIVIDE and column filters; avoid IFERROR, whole-table FILTER and turning blanks into zeros.

Watch out

Making every measure return 0 instead of blank looks tidy but can multiply the cells a visual must compute. Return blank and let the visual hide empty rows.

Common mistakes

Optimizing DAX when the page simply has too many visuals. Check the visual count and the display time first. Thirty visuals each sending queries is a design problem.

Keeping every source column "just in case". Unused high-cardinality columns cost memory and refresh time. Remove them and add them back when a report needs them.

Filtering the fact table with FILTER for a simple condition. A column predicate inside CALCULATE is faster and gives the same result for simple conditions.

Check yourself

What does the Performance analyzer show for each visual?
The time spent on the DAX query, on visual display and on other work, plus the query text you can copy.
Why does splitting a datetime column into date and time make a model smaller?
Each new column has far fewer distinct values, and lower cardinality compresses much better.
Why is [Total Sales] + 0 a performance risk?
It turns blanks into zeros, so visuals compute and show every combination of rows and columns instead of skipping empty ones.

Topic 5 of 6 in Implement and Manage Semantic Models

Test yourself on Implement and Manage Semantic Models

The full Microsoft DP-600 question bank has 648 questions, with timed practice exams and an explanation for every answer.

Subscribe Try Sample Exam

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Microsoft. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.