The dashboard opened in two seconds when it was first published. A year later, with more data, more pages and more measures, it takes thirty, and the 6 a.m. refresh finishes after the executives have already arrived. Power BI slowness rarely has a single cause, but it almost always concentrates in a handful of places. The shortest route is to measure first and change things second.
First: where is the time going?
“Slow” can mean three different things, each with its own fix:
- The page is slow to render when a user opens it or clicks a slicer.
- The refresh is slow or times out.
- The data source responds slowly, which affects both of the above when the model uses DirectQuery.
In Power BI Desktop, the Performance analyzer (on the View ribbon) records, for every visual, the time spent on the DAX query, on rendering and on other work. If the DAX query dominates, the problem is in the model or the measures. If rendering dominates, the problem is the visual itself: tables with thousands of rows, charts with too many data points, pages with too many visuals.
To dig deeper, DAX Studio (a free community tool) can run the query copied from the analyzer and split storage engine time from formula engine time. It also includes VertiPaq Analyzer, which shows the size of every table and column in the model.
The model: a star, not a spreadsheet
The Power BI engine is built for dimensional models. In practice:
- Fact tables hold events (sales, tickets, ledger entries), with keys and numeric values.
- Dimension tables describe those events (customer, product, date, branch) and connect to the fact table through one-to-many relationships.
Signs that a model has drifted away from this:
- one wide table with dozens of descriptive columns repeated on every row;
- bidirectional relationships added to “make the filter work”;
- many-to-many relationships between large tables;
- several date tables, or the auto date/time option turned on, which creates a hidden date table for every date column.
A single calendar table, marked as a date table, with auto date/time switched off, already shrinks the model and simplifies time intelligence measures.
Cardinality: what eats memory
In Import mode, Power BI compresses data column by column. Columns with few distinct values stay small; columns with millions of distinct values compress poorly and slow everything down.
The usual suspects:
- Date and time in one column, down to the second. Split it into a date column and a time column (rounded, if the analysis allows).
- Unique identifiers such as GUIDs, transaction numbers or text natural keys. If no report uses them, drop them.
- Free text such as notes and long descriptions.
- Columns nobody uses. Every imported column takes memory, even if no visual shows it.
Use integer keys for relationships and load only the columns and the history that the analysis actually needs.
DAX: measures that work too hard
Some patterns force the engine to scan a table row by row when it could work on compressed columns instead.
-- Scans the whole Sales table to apply the filter
Sales Northeast =
CALCULATE ( [Total Sales], FILTER ( Sales, RELATED ( Branch[Region] ) = "Northeast" ) )
-- Filters the dimension column directly
Sales Northeast =
CALCULATE ( [Total Sales], Branch[Region] = "Northeast" )
The two versions are not identical in every case: the direct column filter overrides any existing filter on Branch[Region]. If you want to respect that filter, use KEEPFILTERS ( Branch[Region] = "Northeast" ).
Variables avoid evaluating the same expression twice and make measures easier to read:
Margin % =
VAR Revenue = [Total Sales]
VAR Cost = [Total Cost]
RETURN
DIVIDE ( Revenue - Cost, Revenue )
Other things to review:
- complex calculated columns on large tables, which could be built in Power Query or at the source;
- nested iterators (
SUMX,AVERAGEX,FILTER) over the fact table; - measures that apply
ALLto an entire table when it would be enough to remove the filter from a single column, withALL ( Table[Column] )orREMOVEFILTERS ( Table[Column] ).
Import or DirectQuery
The storage mode changes how the report behaves.
Import loads the data into the model. Queries are fast because everything sits in compressed memory, but data only changes on refresh, and model size has limits that depend on your license.
DirectQuery does not store the data: every visual sends queries to the source on every interaction. Performance then depends on the database (indexes, concurrent load, network latency) and on how many visuals the page has. There are also restrictions on transformations and DAX.
DirectQuery makes sense when the data volume does not fit in the model, or when the delay of a scheduled refresh is genuinely unacceptable. Chosen just because someone wants “real time,” it tends to bring slowness for no reason. There are middle grounds: composite models, where dimensions are in Import or Dual mode and the large fact table stays in DirectQuery, and aggregation tables that answer summary queries without touching the source.
If the model uses DirectQuery against SQL Server, look at the database too: the generated queries show up on SQL Server and may need indexes, including columnstore indexes on large fact tables.
Incremental refresh
Reloading five years of sales every day just to add yesterday is wasted effort. Incremental refresh splits the table into date-based partitions and refreshes only the most recent ones.
To set it up, create the RangeStart and RangeEnd parameters in Power Query, with the date/time type, filter the table’s date column with them, and define the policy on the table: how much history to keep and how much to refresh on each run. The policy takes effect once the model is published to the service.
What breaks this most often is query folding. The date filter has to be translated into the query sent to the source. If an earlier Power Query step prevents that, Power BI reads the whole table and filters afterward, and the benefit is gone. With a SQL source, the “View Native Query” option on the filter step helps confirm folding.
The gateway: the bottleneck nobody checks
On-premises sources reach the Power BI service through an on-premises data gateway. It is a server like any other, and it was often installed on whatever machine happened to be free.
Check:
- whether the gateway runs on a dedicated machine with enough CPU, memory and disk for the volume, without competing for resources with the database itself;
- the network distance to the sources, since the gateway should sit close to the data;
- whether it is up to date and whether there is more than one node in a cluster, so you do not depend on a single machine;
- whether many refreshes are scheduled at the same time, competing for the same gateway and the same source.
The gateway’s logs and performance counters show whether it is saturated during refresh windows.
A practical order
- Measure with the Performance analyzer and find the slowest pages and visuals.
- Check the model size and remove unused columns and history.
- Fix the modeling: star schema, single-direction relationships, one date table.
- Rewrite the most expensive measures.
- Review storage mode and incremental refresh.
- Only then consider more capacity. More capacity on top of a poor model usually just delays the problem.
Want help with your model?
If your reports are slow and the team is not sure where to start, an assessment measures the model and its sources to locate where the time is spent. Learn about our business intelligence and Power BI service or request an assessment.