Why Power BI reports are slow and what to check first

A Power BI report that takes thirty seconds to open, or a refresh that fails three mornings a week, is rarely a mystery once it has been measured. The difficulty is that most people start by changing things - a visual removed, a measure rewritten, a bigger capacity bought - before finding out which of the five usual causes they have. This article lists the checks in the order that isolates the cause fastest. Three of them take under an hour with tools that are free.

First, decide which kind of slow you have

There are three different complaints that all get called "slow", and they have different causes. A report that is slow to open and slow to interact with is a query problem: the visuals are asking the model questions it answers slowly. A refresh that takes hours or fails is a load problem: the data is arriving slowly, or not at all, from the source. A report that is fast for one person and slow for another is usually a capacity or a row-level-security problem. Establish which one you have before measuring anything, because the tools differ.

Check 1: model size and cardinality

Open the model in DAX Studio or use the Vertipaq Analyzer view in Tabular Editor and look at two numbers: total model size and the size of the largest columns. The largest columns are almost always high-cardinality text or datetime fields - an invoice number, a free-text description, a timestamp to the second - that are stored in the model because they were in the source, not because a visual uses them. A model with a fact table of ten million rows should usually be well under a gigabyte; if it is several gigabytes, the columns are the reason, and removing or rounding them is the cheapest improvement available.

The other size question is whether the model is a star schema. A model that imports the source tables as they are, with many-to-many relationships and bi-directional filters to make them join, will be slow in ways no amount of DAX tuning fixes. If the relationships view looks like the source system's diagram, the model needs restructuring, not optimising.

Check 2: the slowest visuals and their DAX

Performance Analyzer, built into Power BI Desktop, records how long each visual takes and splits the time between the DAX query and the rendering. Sort by duration. Typically two or three visuals account for most of the page time, and each of them is running a measure that iterates a large table row by row - a SUMX over the fact table, a FILTER inside CALCULATE that scans every row, or a measure that calls another measure that does either. Copy the query into DAX Studio, run it with server timings on, and look at whether the time is spent in the storage engine (a data problem) or the formula engine (a DAX problem).

The fix is usually to rewrite the measure so that the storage engine does the work: replace FILTER over a table with a filter on a column, pre-calculate a flag in Power Query rather than testing it in DAX, and avoid measures that need the previous row. This is where most of the improvement in a query-slow report comes from, and it is also where a rewrite can change a number, so every rewritten measure is reconciled to the original before it ships.

Check 3: query folding

For a refresh-slow report, the first question is whether the Power Query steps are folding: being translated into a query the source runs, rather than pulling every row into Power BI and transforming it there. Right-click the last step of each query and check whether "View native query" is available. If it is greyed out, the step before it broke folding, and everything from that step on is running on the gateway machine over the full data set. Common culprits are a custom column, a change of data type after a merge, or a step that references another query.

Sources matter here. SQL Server, Business Central, SAP HANA and most databases fold well. Tally over ODBC, Zoho Books over its API and any source that arrives as an Excel export do not fold at all, so the transformation has to be kept light and the refresh incremental rather than full.

Check 4: refresh pattern

A model that reloads all of its history every night is doing work it does not need to do. Incremental refresh - loading only the recent partitions and keeping the older ones - is available on Pro as well as Premium Per User and Fabric capacities, and it turns a two-hour refresh into a ten-minute one when the history is large and stable. It needs a date column that the source can filter on, so it is worth checking the source supports it before designing around it.

The refresh history in the Power BI service shows the duration of each refresh and where a failure occurred. A pattern of failures at the same time each day usually points at the source (a backup window, a maintenance job) rather than at Power BI.

Check 5: the gateway and the capacity

If the source is on-premises - a Tally server in the office, SAP Business One on a local SQL Server, a shared drive of Excel files - the refresh runs through an on-premises data gateway, and the gateway machine is often the bottleneck: an old virtual machine with too little memory, a gateway that has not been updated in a year, or one machine serving every refresh in the business at the same hour. The gateway logs show queue time and mashup memory use; if the refreshes are queuing, spreading the schedule or adding a second gateway in a cluster is the fix.

Capacity is the last thing to check, not the first. On a Fabric capacity the metrics app shows whether the capacity is being throttled; if it is, the question is still which reports are consuming it, and the answer is usually the same two or three visuals from Check 2. Buying a larger capacity before running the earlier checks tends to buy a few months before the same problem returns.

Patch or rebuild

After the five checks there is enough information to decide. If the model is a reasonable star schema and the problems are a handful of measures and a folding break, it is a patch: a few days of work and a reconciled result. If the model imports source tables as they are, carries columns nobody uses and relies on bi-directional relationships to make the numbers add up, the honest answer is a rebuild of the model with the existing visuals pointed at it. The Power BI performance optimisation page on this site describes the fixed-price health check that runs these checks and delivers that recommendation in writing, so the decision is made on measurements rather than on a guess.