A Power BI report that takes thirty seconds to open: look in the model, not in the visual

A report that takes thirty seconds to open ends up not being opened at all. It is the most common failure mode of a business intelligence project: the report is right, it is complete, and nobody consults it any more.

The reflex is to simplify the page. We systematically start elsewhere, in the model, because that is where the five causes that explain most of the cases we take over are found.

1. Columns that nobody uses

Power BI compresses data column by column. A high-cardinality column, such as an order number, a timestamp to the second or a technical identifier, compresses poorly and weighs heavily in memory, even when no visual reads it.

The typical case is a fact table imported as it stands from the ERP, with its hundred and twenty columns of which twelve are actually used.

What we do: we remove unused columns at the source, in Power Query, not in the model. And we replace full timestamps with a date on one side and a time on the other: two low-cardinality columns compress far better than a single datetime that is unique on every row.

On a table of ten million rows, this one operation commonly halves the size of the model.

2. Bidirectional relationships

Bidirectional cross-filtering elegantly solves certain scenarios, and it is expensive. At each calculation, the engine must propagate filters in both directions, which multiplies the work and sometimes introduces ambiguities that produce wrong results.

What we do: we switch all relationships back to single-direction filtering, from the “one” side to the “many” side. Where bidirectional filtering seemed necessary, we replace it with a CROSSFILTER function placed in the one measure that needs it. The cost becomes local instead of permanent.

3. A missing or incorrectly marked date table

This is the most frequent cause and the quickest to fix. Without a dedicated date table, marked as such in the model, the time-shifting functions (SAMEPERIODLASTYEAR, DATEADD, TOTALYTD) cannot rely on the optimisation designed for them. They fall back on a generic calculation, much slower, and sometimes wrong on shifted fiscal years.

What we do: a continuous date table covering the whole history, with no gaps, marked as a date table, and related on the date that carries the business meaning: the invoice date, not the entry date. It is a quarter of an hour of work and the effect is immediate.

4. Calculated columns instead of measures

A calculated column is evaluated at every refresh, for every row, and its result is stored. A measure is evaluated on demand, on the displayed context. On a large fact table, the difference is considerable.

The textbook case is a Margin = [Price] - [Cost] column placed on ten million rows, when a SUMX measure or, better, two sums subtracted from each other would return the same result without storing anything.

What we do: we migrate to measures everything that can be aggregated. We keep as calculated columns what is used to filter or group, such as an ageing band or a derived category, because those uses need a materialised value.

5. Measures that iterate needlessly

SUMX, FILTER and their cousins go through the data row by row. This is essential in some cases and pointless in many others.

Writing:

France revenue = CALCULATE ( [Revenue], FILTER ( Sales, Sales[Country] = "FR" ) )

forces a full scan of the sales table. Writing:

France revenue = CALCULATE ( [Revenue], Sales[Country] = "FR" )

lets the engine apply the filter directly on the column, which it does much faster. The result is identical, the cost is not.

What we do: we review the measures looking for FILTER over an entire table where a column filter would suffice. It is the fix that, on its own, saves the most seconds.

Where a model that holds up lives

The five fixes above assume there is a place to set the rules once and for all. That is the role of the modelled layer, the one we call gold, and it is what distinguishes a data chain from a collection of reports.

BronzeIngestion

Raw data, exactly as the systems produce it. ERP, CRM, Dataverse, SQL databases, files.

SilverCleaning

Cleaning rules and business alignment. This is where the charts of accounts of six subsidiaries are reconciled.

GoldSemantic model

Aggregated and optimised data. One source of truth, shared by every report and every department.

Measure before fixing

All of these fixes are good in general; they are not all useful on your report. Before touching anything, we look at two things.

Power BI Desktop's Performance Analyzer gives, visual by visual, the time spent on the query and the time spent on rendering. A visual with eight seconds of query and two hundred milliseconds of rendering points to the model without ambiguity.

DAX Studio gives the detail: which query, which execution plan, how many rows materialised. That is where you see that a measure produced an intermediate table of four million rows to return a single number.

Without these two measurements, you fix by guesswork, and often spend a day optimising what cost two hundred milliseconds.

What to remember

A report's slowness rarely comes from the number of visuals. It comes from columns that were not removed, relationships that are too permissive, a missing date table, calculations materialised for no reason, and measures that iterate when a filter would suffice.

All five can be fixed, and the order matters: measure first, otherwise you will optimise the wrong place.