The question that matters

Insights

Power BI rarely fails outright. It degrades, in places that are hard to attribute: a refresh that needs twice the memory you budgeted, a storage mode that quietly changes behaviour under load, row-level security that a workspace role bypasses. This is what tends to break once it becomes the reporting layer for a whole organisation, why it breaks, and which parts you can design away before they cost you.

Power BI is very good at the first hundred reports and much less forgiving at the point where it becomes the organisation's reporting layer. The question worth asking before you commit is not whether it can handle your data, because it almost certainly can. It is which of its failure modes are inherent to a hosted, capacity-metered service that Microsoft operates, and which are ones you will build yourself through modelling, refresh design and governance. The two have completely different fixes, and conflating them is how teams end up buying more capacity to solve a problem that was a bidirectional relationship.

Almost everything expensive traces back to one decision: whether the semantic model is a shared engineering artefact or something each report brings its own copy of. VertiPaq, the in-memory columnar engine underneath, and DAX on top of it are both built around a star schema. Narrow dimension tables, low-cardinality keys, facts in the middle. Hand it a heavily normalised snowflake, or one wide flat table straight out of a warehouse view, and you get no error at all. You get something that works, and then degrades in ways nobody can attribute to a cause.

Size is driven by cardinality, not row count. A fact table of integers and foreign keys compresses beautifully at a scale that surprises people. A far smaller table carrying GUIDs, full datetimes to the second and unrounded floats will be larger and slower. The remedies are unglamorous and they work: drop surrogate keys nobody queries, split datetime into a date key plus a separate time column, round decimals to the precision the business actually reports, and remove columns rather than hide them, because hidden columns still occupy memory.

Two defaults cost more than people expect. Automatic date/time silently creates a hidden date table for every date column in the model. And calculated columns are materialised at refresh and typically compress worse than the same column imported from the source, so the cheap-feeling DAX column is the expensive one. Push it upstream, or make it a measure.

For query time, the thing to understand is that DAX evaluation splits between a multi-threaded storage engine and a single-threaded formula engine. Measures that force row-by-row evaluation back into the formula engine, typically nested iterators or conditional logic inside an iterator over a large table, are why one visual on a page takes eight seconds while everything around it is instant. Server timings in DAX Studio will show you the callbacks. Nothing in the authoring experience will.

My own opinion, held fairly strongly: a model per report is the most costly habit in Power BI, and it is organisational rather than technical. The pattern that holds is a small number of governed models in tightly-controlled workspaces, thin reports connected live, and endorsement used so people can tell the sanctioned model from the eleven forks of it.

A full refresh needs room for the model that is currently serving queries and the new one being built alongside it. Budget for roughly double the model's footprint plus headroom, and expect the first hard wall you hit to be a refresh failure rather than a query failure. Incremental refresh, which partitions on RangeStart and RangeEnd, is the real answer, but it only pays for itself if that filter folds into the source query.

Query folding is the load-bearing concept in Power Query and the one most often misunderstood. When a step folds, it becomes part of the query sent to the source. When it does not, the mashup engine pulls the whole table and does the work locally. A native query used as the source, custom columns with no SQL equivalent, some merge shapes, and privacy-level mismatches that surface as Formula.Firewall errors will all break folding. The results stay correct, which is exactly why this goes unnoticed until the refresh window does not fit.

If you are refreshing from on-premises sources, the gateway is doing the mashup work, and its CPU, memory and disk spooling are usually the actual constraint rather than anything in the service. A single node is both a queue and a single point of failure. Cluster it, and monitor the gateway host itself, not only the refresh durations you see in the portal.

Two operational details that bite later. Data source credentials are bound to whoever configured them, so refreshes break when that person leaves or rotates a password; service principals are the fix, and they depend on a tenant setting your admin controls. And scheduled refresh frequency is capped differently per tier, with the per-user tier allowing only a handful per day, capacity allowing considerably more, and the XMLA endpoint or REST API allowing effectively as many as you can justify. If you find yourself adding scheduled refreshes to chase freshness, you want hybrid tables or DirectQuery on the hot partition instead.

The choice of storage mode is the most consequential one in the model and the least visible afterwards.

Composite models let you mix all three per table, which is genuinely useful and also the fastest way to build something nobody can reason about in six months. If you use them, write down why each table is the mode it is, in the model, not in a wiki.

A capacity is a pool, and background work and interactive work draw from the same budget. The platform smooths background usage across a long window and interactive usage across a short one, then throttles when you overdraw, first by delaying requests and eventually by rejecting them. The user-visible symptom is that Power BI is slow today with no identifiable culprit, because the culprit is another team's refresh overlapping your morning.

Models are also evicted from memory when the capacity comes under pressure, and the next query pays the load cost. A great deal of reported first-thing-in-the-morning slowness is eviction, not the report.

The pitfall I would rank first for anyone about to go organisation-wide is that observability is opt-in. Install the capacity metrics app, and route semantic model logs to Log Analytics if your tenant allows it, because without query-level logging you are diagnosing by anecdote. Do this before you need it, not during the incident.

Capacity cost has a shape worth understanding even though I will not quote a figure: you are buying a throughput ceiling over time, not queries. An idle capacity costs what a busy one costs unless you pause it, and autoscale converts a performance problem into a spend problem quietly. That makes capacity sizing an engineering decision with a finance consequence, which is not where most organisations put it.

Row-level security is a set of DAX filters evaluated on every query, and it has two surprises. The first is that workspace roles override it: users with edit rights on the workspace containing the model are not subject to its RLS at all. Organisations usually discover this when someone in the analyst group sees the whole payroll. The arrangement that holds is the model in its own workspace with deliberately small membership, and consumption through an app or a read-only role.

The second is cost. Dynamic RLS, matching the signed-in user against a permissions bridge table, is the standard pattern and also where query performance goes to die, because that filter is applied to every query on every visual. Combine a high-cardinality permissions table with bidirectional relationships and you have a model that is both slow and ambiguous about what it filters. Bidirectional relationships plus RLS is a known bad pairing on both counts.

Finally, be honest with yourself about what RLS is. It restricts rows, not structure, and object-level security covers some of the rest but is awkward to maintain outside of external tooling. Anyone who can see a number can put it in a spreadsheet. Export limits and sensitivity labels reduce casual leakage; they are not a perimeter. If data genuinely must not leave one, that control belongs at the model and workspace boundary, and ultimately at the architecture, rather than in the report.

A .pbix is a binary file, so two people editing the same report means one of them loses their work and finds out later. The Power BI Project format, which writes the model out as TMDL text, plus Fabric's Git integration, finally makes diffing and merging possible. If you are standing up an enterprise deployment now, start there. Retrofitting source control onto three years of binaries is genuinely miserable work.

There is no built-in test framework, so you assemble one or you accept that regressions ship. What practitioners actually use is the Best Practice Analyzer in Tabular Editor for model hygiene, stored DAX queries as regression checks on measure results, and server timings for performance. Deployment pipelines with parameter rules handle environment parity; without them, someone will hand-edit production, and it will be a Friday.

Two traps catch nearly everyone once. Date and time functions evaluate in the service's time zone rather than your analyst's, so a report that is correct in Desktop reports yesterday in production. And a model authored under one regional setting can fail to parse dates on refresh in the service while working perfectly on the developer's machine. Both are cheap to fix and disproportionately expensive to diagnose. Add to that the mundane one of Power BI Desktop version drift across a team, where a file saved in a newer build simply will not open in an older one.

Do four things before Power BI becomes load-bearing. Decide the storage mode per table deliberately and write down why. Size the refresh, not the model, because that is the wall you hit first. Put the governed models in workspaces whose membership you would defend in an audit, since workspace roles outrank row-level security. And turn on the monitoring while everything is still fast. Then separate the two categories honestly: the modelling, refresh design, security layout and source control are all yours to get right, and they decide whether this holds up. What is not yours is that this is a hosted, metered service operated by someone else, with an on-premises option that is a lagging subset rather than the same product. If your real constraint is that the data cannot leave your estate, that is an architecture decision to take at the start, not a setting to find later.

Talk to us about this