Power BI performance and models, answered

Usually not because of data volume. A few million rows is small for the engine. Reports slow down when the model is a snowflake of chained lookups, when relationships filter in both directions, when calculated columns are stored for every row, when the whole history is reloaded every night, and when the landing page scans the detail table to draw a handful of bars. Each of those is a modelling decision, and each has a fix. The five fixes walkthrough goes through them on Microsoft's Contoso sample.

One fact table (sales, tickets, readings) surrounded by clean dimension tables (date, product, store, customer), one hop from any filter to the fact. The alternative, a snowflake where product points to subcategory points to category, makes every category filter walk three tables before it touches a sale. Flattening the joins into the dimensions, in Power Query or in the warehouse view, runs them once at refresh instead of on every click.

They feel convenient: a slicer on one dimension trims another. The cost is that every filter now propagates both ways through the fact table, on every visual, on every click, and the engine has to resolve ambiguous paths. Set every relationship to a single direction, dimension to fact, and ask for the other direction in the one measure that needs it with CROSSFILTER. A relationship setting is global; a CROSSFILTER in a measure is a decision you can see.

Measures, almost always. A calculated column is computed and stored for every row at refresh, and a derived decimal has as many distinct values as the data allows, which is the worst case for the column store. A measure runs only for the cells on screen. Keep calculated columns for things a slicer needs to filter on; everything else is a measure.

A policy on a table that keeps older data as stored partitions and reloads only a recent window each refresh, defined with the two reserved parameters RangeStart and RangeEnd in Power Query. A model that reloads ten years of history every night to pick up yesterday's orders is doing hours of work to change one day. With a real range the refresh drops to minutes and a full reload becomes something you choose, not something you wait for.

Summary tables grouped by the columns the busy pages filter on (date, store, category), marked in the model as aggregations of the detail table. The engine answers from the summary when it can and falls through to the detail when it cannot. The report does not change; the landing page stops scanning millions of rows to draw a few bars, and the detail stays for drill-through.

A page should open and a slicer should respond in a few seconds or less on a model of ordinary size. If it is noticeably slower, the model is doing work it does not need to. The figures in our walkthrough (a report of that shape opening in about 40 seconds before the fixes and under three after) are typical for a model of that shape, not a benchmark, and your numbers will differ. The order of the fixes will not.

The layer between the tables and the visuals: the relationships, the measures with their written definitions, the display folders, the row-level security roles. One semantic model can serve many reports, which is how two teams stop showing two different "revenue" numbers. We build it as a star, with one written definition per number, and treat it as the product; the reports are views on it.

If you are already on Power BI Premium capacity or standardised on Microsoft, Fabric adds a lakehouse, pipelines and Direct Lake under the same licence and is worth a look. If your platform is Databricks, the gold layer and a semantic model on top already give you the same outcome. We work with both; the answer depends on what you own and what your team can operate, and we put it in writing after the scoping call.

Import for most models: fastest visuals, full modelling features, refreshed on a schedule with incremental refresh. DirectQuery when data must be live and the source can take the load, accepting slower visuals and limited modelling. Direct Lake, on Fabric, reads Delta tables directly without import and sits between the two. We default to import with incremental refresh and move only when a real latency need exists.

Because each report carries its own definition. One excludes refunds, one counts them; one uses order date, one uses ship date. The fix is a single semantic model with one measure per number, each with a written definition the finance team has agreed, and reports that only use those measures. If a figure needs a footnote, the model carries it; the report shows it.