Power BI Performance Optimisation

Slow Power BI dashboards are rarely a Power BI problem. They are almost always a data model problem that Power BI is honestly surfacing. The platform is fast when the model is right and punishingly slow when it isn’t, which is useful information if you treat it as such — and a source of constant frustration if you treat the symptom (the slow dashboard) as the problem.

Most of the optimisation work we do on inherited Power BI estates is undoing decisions taken three years earlier when the model was first built. Calculated columns where measures would have done. Snowflake schemas imported verbatim from the source database. Visuals dependent on row-by-row context transitions across hundreds of thousands of records. Each decision was reasonable in isolation; together they compound into a model that no amount of DAX tuning will rescue.

The principles that distinguish a Power BI model that scales

The optimisation surface has ten meaningful levers; in our work, three of them do most of the work on most projects. The remaining seven matter, but the three below are where we focus the diagnostic energy first.

Model design beats query optimisation every time.

A star schema with the right grain, the right relationships, and the right calculated dimensions will outperform a snowflake schema with optimised DAX in every benchmark we have run. We refuse to start a Power BI optimisation engagement at the DAX layer; we start at the model and only descend into DAX once the model is sound.

Profile, don't guess.

DAX Studio is non-optional. The number of optimisation hours we have seen wasted on the wrong measure because the team trusted their intuition about where the bottleneck lived is uncomfortable. The query plan reveals what is actually slow; the difference matters when the engagement is billed by the day.

Constraints are features.

”Fewer than eight visuals per page” is a hard performance constraint, not an aesthetic one. “Import mode by default” is a 10x performance advantage over DirectQuery on the same data. “Row-Level Security via roles, not DAX filters” is a query-plan optimisation. We treat these as design constraints rather than guidelines; the dashboards that follow them are fast, the ones that bend them are not.

Underneath the three sits a discipline the commandments don’t make explicit: cadence-fit modelling. A model built for hourly refresh against a fact table of 50 million rows is a different model from one built for daily refresh against 5 million. Incremental refresh, aggregation tables, and the Import-vs-DirectQuery decision all hinge on the cadence question — and getting it wrong at modelling time produces dashboards that perform well in dev and stall in production. We size the cadence before we size the model.

The ten commandments

DAX

Use measures, not calculated columns

Measures calculate at query time on aggregated data; calculated columns bloat model size by storing values for every row, slowing down performance.

Deliverable Smaller model, faster refresh.

Modelling

Embrace the star schema

It is the most performant structure for BI tools, avoiding the complex, multi-hop joins of normalised schemas.

Deliverable Predictable query plans.

Refresh strategy

Use incremental refresh

Configure incremental refresh for large fact tables to minimise refresh times and resource consumption.

Deliverable Hourly cadence on tables that would otherwise require a nightly run.

Power Query

Enforce query folding

Where possible, push transformations back to the source database during Power Query steps so the warehouse does the work.

Deliverable Transformations run in the source database, not in memory.

Modelling

Create aggregation tables

Dramatically speed up high-level dashboards by pre-calculating summary-level data.

Deliverable High-level dashboards resolve from pre-aggregated data.

Security

Implement RLS via roles

Use the built-in Row-Level Security engine, which is more secure and performant than applying complex DAX filters in each measure.

Deliverable Access control that doesn't distort the query plan.

Report design

Limit visuals per page

Each visual fires at least one query. We aim for fewer than eight per page to keep the experience responsive.

Deliverable Fewer queries fired on every page render.

Storage mode

Default to Import mode

Use DirectQuery sparingly and only when near real-time data is genuinely required, as it is significantly less performant.

Deliverable In-memory speed instead of per-query round-trips.

DAX

Use variables in complex DAX

Store intermediate calculations in variables to improve readability, debugging, and query performance.

Deliverable Readable, debuggable, faster measures.

Diagnosis

Profile performance with DAX Studio

Don’t guess where bottlenecks are; analyse query plans and identify the root cause of slow performance.

Deliverable Optimisation hours land where they pay back.

A caveat

The ten commandments optimise an existing Power BI implementation. They do not ask the prior question: is Power BI the right tool for this use case? For ad-hoc analytical exploration, Tableau and Sigma have stronger interactivity surfaces. For embedded analytics inside operational applications, Looker is often a better fit. For ML pipelines or compute-heavy feature engineering, Power BI is the wrong layer entirely — that work belongs in the warehouse or a notebook. The Data Foundations pillar we run with clients includes the BI-tooling fit assessment.