Optimizing Model Performance

What it is

A slow report usually comes from a big model, a poor model design, an inefficient measure or too many heavy visuals. Optimising starts by making the model smaller, then measuring which visuals, measures and relationships are slow, using Performance Analyzer and DAX query view. This page covers both the fixes and the tools.

How it works

Remove what you do not need

Import models are held in memory and compressed by column, so what you remove saves space and speeds queries.

Hiding a column does not remove it from memory. Only removing it from the query does.

Reduce granularity

Granularity is the level of detail of a table. A fact table with one row per second may have millions of rows. If reports never need less than one day, aggregate to the day in Power Query with Group By, which can shrink the table by a large factor. Related ideas:

• Split a Date/Time column into a Date column and a Time column, so each has far fewer distinct values.
• Round numeric values that do not need precision.
• Use user defined aggregations, a summary table at a higher level that Power BI uses automatically before it goes to the detail table.

Design choices

• Use a star schema with one to many relationships and single direction filters.
• Avoid bi directional filters and many to many relationships unless needed.
• Keep a small set of visuals per page. Each visual sends its own query.
• For DirectQuery, check that Power Query steps fold and think about query reduction settings, such as an Apply button on slicers.

Performance Analyzer

Performance Analyzer in Power BI Desktop (View tab) records how long each visual takes to load. Select Start recording, then Refresh visuals or interact with the report, and read the results for each visual.

Expand a visual to see its timings, copy the query for that visual, and export the log as JSON. Sort by duration, find the slowest visual, then decide: simplify the visual, fix the measure or change the model.

DAX query view

DAX query view (one of the views on the left of Power BI Desktop) lets you write and run DAX queries against the model. Start a query with EVALUATE, and use DEFINE to define measures or variables in the query without changing the model.

DEFINE
    MEASURE Sales[Total Sales] = SUM ( Sales[Amount] )
EVALUATE
    SUMMARIZECOLUMNS ( 'Date'[Year], "Sales", [Total Sales] )
ORDER BY 'Date'[Year]

Paste a query copied from Performance Analyzer, run it, change the measure, and compare the timing. When a defined measure works better, use Update model with changes to write it into the model. Right click a table or column for Quick queries, which generate statistics or sample data queries for you.

Fixing slow measures

• Replace repeated calculations with variables.
• Filter a column inside CALCULATE instead of the whole table with FILTER.
• Avoid nested iterators over large tables.
• Reduce use of DISTINCTCOUNT on very high cardinality columns where an alternative exists.

The Optimize ribbon in Desktop has options such as pausing visuals while you edit, which stops queries while you build a report.

A worked scenario

A report with a 120 million row table takes 20 seconds to open. The analyst removes two unused text columns, filters out data older than five years, and aggregates the order lines to one row per product per day with Group By. She turns off auto date/time. Performance Analyzer then shows one matrix with a 6 second DAX query, so she copies its query into DAX query view, replaces a FILTER over the whole table with a column filter, and the query drops to under a second.