That is (meant to be) a profit and loss statement. It is not loading slowly. It has been refused. The query has failed and a user wants to know why.

Every number in this article comes from a file you can download and open yourself. The measurements are Performance Analyzer numbers, taken in Power BI Desktop, on nine builds of a similar financial statement. You will not get identical figures on your machine, but you will get similar ones, and the differences between the builds are large enough that the ranking does not move.

The month end problem

A slow P&L is not evenly slow. It is slow on the first working day of the month, when the whole finance team opens the same report at the same time, and when every scheduled refresh in the tenant is already running.

Two things go wrong at that point, and they are different problems. The first is interactive delay: the report loads, eventually, and people wait. The second is interactive rejection: the capacity declines to run the query at all, and the user gets an error where the numbers should be.

The second one is the expensive one. Organisations buy additional capacity to survive month end. That is a real line item, paid every month, to work around a handful of visuals that ask for far more than they need.

The measurements below suggest that in a lot of cases the capacity was never the problem.

The visual that does it

The shape that hurts is months across the columns and measures down the rows. January through December, then year to date, year to go and full year, with the statement lines underneath.

Fifteen period columns come from a calculation group. Twenty-eight measures sit on the rows. That is 420 cells, and the engine treats them as 420 independent expressions, because it compiles one plan that has to be capable of producing every row expression against every column expression.

Not all twenty-eight are doing work. Seven of them are the section captions, the rows reading Income, Cost of Sales, Gross Profit and so on, and each of those is a measure defined as BLANK ( ) whose only job is to put a label on the page. It is fair to ask whether the engine bothers with them.

It does, and they are cheap but not free. Adding those seven blank measures to a nine measure query on this model costs about 5%, roughly 8 ms each against roughly 125 ms for a measure that actually computes something. Run the seven on their own and they take 95 ms to return no rows at all.

So the working count is twenty-one real measures across fifteen columns, and those twenty-one are exactly the ones carrying a dynamic format string that reads its own measure back so it can choose between $1.2M and $340K. A format string is evaluated once per rendered cell, which is a second evaluation of the measure for every cell you can see.

On the lab model this build measures 11,065 ms.

Where the time actually goes

Before changing anything, it is worth knowing which part of the visual is expensive, and Performance Analyzer answers that on its own. Open it from the View ribbon, press Clear, press Refresh visuals, then expand the row for the statement.

The calculation group matrix comes back at 11,918 ms in this run, and the split is decisive:

Ninety-eight per cent of the wait is the DAX query. Rendering 420 cells costs 117 ms. The engine is not struggling to draw a table, it is struggling to answer 420 questions.

That single reading rules out most of the things people try first:

• More capacity buys throughput for a formula engine problem that is about how many separate expressions get evaluated, not how much hardware is available. At month end there is no spare capacity anyway.
• Aggregation tables make scans cheaper but the scan was never the bottleneck.
• Reducing the data works on the same assumption, and runs into the same answer.

It is also worth noting what the Deneb column shows, because it is the shape of the fix. Its query is 33 ms. Its largest single cost is “Other”, the custom visual’s own overhead, and that is the honest trade: the engine work nearly disappears and a little visual overhead comes back.

When the rows are accounts, fix the model

Most P&Ls are simpler than the monthly one. Every statement line is a set of general ledger accounts, and the classic build dispatches on a disconnected table:

P&L Value =
SWITCH (
    SELECTEDVALUE ( 'P&L Rows'[Line] ),
    "Total Income",   [Income],
    "Cost of Sales",  [COGS],
    "Gross Profit",   [Income] - [COGS],
    ...
)

It works, it reads well, and it measures 4,978 ms.

The fix is to stop dispatching and start filtering. Build a physical table with one row per statement line and account pair, relate it to the fact table, and put the line column on the rows:

A subtotal is now just a line with more rows behind it. Total Income has four, Gross Profit nine, Net Profit twenty-two. The row axis has become a grouped column, so the storage engine buckets the rows in a single scan and the row count stops costing anything. There is no dispatch DAX left in the model at all.

4,978 ms becomes 407 ms, and it is still a native table visual that anyone on the team can edit.

This is the first thing to reach for. It is a one off model change, every future visual over that model inherits it, and it introduces nothing new to maintain.

The rows that are not accounts

Then comes the statement that defeats it.

Some lines are not a set of accounts. Gross Margin % is a ratio of two rows above it. Income per Trading Store is a division. Trading Stores is a distinct count. On the thirteen line statement in the lab, eight rows are like this: four ratios, three per unit metrics and a count. No filter on any table produces them, so the bridge is simply unavailable.

That leaves dispatch, and the lab builds it four ways.

The first result is the interesting one, and it is worth pausing on.

Calculation groups are widely presented as the modern, clean replacement for a large SWITCH, and the performance claim is usually made in passing. A 2026 write up on this exact scenario, a dynamic matrix, states that “because only the selected Calculation Item is evaluated, the query footprint is a fraction of what it was under the SWITCH approach”, and offers no timings. The mechanism usually given for it is that SWITCH evaluates every branch on every query, which is not what the DAX reference says: “as soon as one value matches, the corresponding result is returned, and other subsequent values aren’t evaluated”.

Performance must be measured. Do not take for granted that a solution using calculation groups is faster than other alternatives.

SQLBI, using calculation groups to selectively replace measures

So the lab measures it. It removes SWITCH entirely and puts a second calculation group on the rows. Same 182 cells, same leaf measures, same numbers, no branch logic anywhere in the model.

It comes out more than five times slower than the SWITCH it replaced.

Thirteen calculation items crossed with fourteen calculation items is still 182 independent expressions. Cost tracks instantiation count, not branch count. A shallow SWITCH is evaluated once; a calculation group rewrites the whole cell expression, per item, every time. Stack two of them and the higher precedence group’s format string wins and re-evaluates on top of everything else.

None of which is an argument for going back to SWITCH everywhere. It is an argument that “calculation groups are faster” is a guide rather than a rule, and on this shape it doesn’t stack up.

At 1,484 ms the best native answer is respectable, but still slow. With heavy usage or very large datasets this can still cause issues.

The reduction, Deneb’s flawless victory

In essence, a financial statement is mostly arithmetic performed on a small set of numbers.

Look again at the monthly matrix. Fifteen columns, but only twelve of them are months: year to date, year to go and full year are sums of the other twelve. Twenty-eight rows, but seven are blank captions and only ten of the rest are base measures. The remaining eleven are differences, ratios and percentages of those ten.

So the engine only ever needed 12 rows by 10 measures. One SUMMARIZECOLUMNS, one scan shape, no calculation group anywhere near it:

SUMMARIZECOLUMNS (
    'DimDate'[Year],
    'DimDate'[MonthOfYear],
    "Income Act", [Income Act],
    "Income LY",  [Income LY],
    "COGS Act",   [COGS Act],
    ...
)

Everything else is built in the visual. Every derived column, every variance, every percentage, every number format, every red negative and every bold subtotal.

That is what a Deneb grid is for. Not because a custom visual renders faster than a native one, but because it is the only option that lets the derived rows leave the formula engine entirely.

11,065 ms becomes 206 ms.

The same move on the thirteen line statement takes the best native build from 1,484 ms to 344 ms.

Zero formatting engine costs

The twenty-one dynamic format strings are worth their own note, because they are pure profit when they move.

In the model, each one is a formatStringDefinition that reads its own measure back so it can auto scale between millions and thousands. That is a second evaluation of the measure, for every cell that displays it.

In the spec, the same rule is a ternary:

isMoneyRow ? pbiFormat(datum.value, '$#,##0,,.0"M"')
: isPctRow ? pbiFormat(datum.value, '0.0%')
: pbiFormat(datum.value, '#,##0')

Zero engine cost. Colour works the same way: red negatives, emphasis on the primary columns, black on subtotals, all static conditions in the spec rather than measures the engine evaluates per cell.

One small trick that avoids an extra query. Add 12 * Year + Month - MonthOffset as a Min aggregation. It is constant on every row and hands the spec today’s absolute month index, so year to date and year to go become arithmetic rather than a REMOVEFILTERS probe.

Where the finish line actually is

The number worth looking at is not the 206 ms on its own. It is the 206 ms next to everything else on the same page.

In the same Performance Analyzer run, the four slicers on that page measure 277, 330, 347 and 380 ms. The image, 112 ms. The text boxes, 94 to 96 ms each.

The financial statement is now the fastest query on its own page. It has stopped being the thing anyone waits for, which is the point at which you can stop optimising.

Proving the numbers did not change

Rewriting how every cell in a financial statement is calculated is not a change anyone should take on trust.

The lab runs two independent gates on the monthly grid. The first recomputes all 315 cells through the original calculation group, then again by deriving them the way the spec does, and asserts they agree to a relative 1e-9. The second checks the spec’s rules against a separate implementation of the same arithmetic, on synthetic rows built to hit the awkward cases: a zero denominator, a blank numerator, and months with no actuals at all.

Both return zero mismatches, and the assertions earned their keep. On the odd rows grid the two DISTINCTCOUNT columns need REMOVEFILTERS ( 'P&L Lines' ). Without it they inherit the grouped line filter and count only the stores that transacted on that line, which quietly changes every per unit row in the table. The numbers still look entirely plausible. Nothing but a tie out would have caught it.

The one thing to know before you build it

Deneb gives you exactly one query role, called dataset. Grouping columns and measures all land in that single well, and the spec reads them off datum by name.

The field name the spec sees is the display name in the Values well, not the field’s real name. If a measure is called NM Amount and the spec expects datum['Amount'], the projection needs nativeQueryRef: "NM Amount" with displayName: "Amount".

Get that wrong and nothing errors. The query runs, the data arrives under the wrong name, every reference in the spec is undefined, and a null guarded spec renders a perfectly intact empty skeleton with the axes still drawn. It looks like it worked. Check the first cell against the model before trusting any of it.

Two more that cost real time. Flattening the query changes the filter context the measures run in, so only calendar registered columns are safe to put in the group by: on a modern calendar object, an unregistered column makes every time intelligence measure return blank, silently. And DAX blanks arrive in the spec as JavaScript nulls, which coerce to zero, so a month with no actuals will pass an unguarded datum.Month > datum.curP and quietly render the whole year.

When not to do this

The Deneb solution is not the answer to everything, there are drawbacks to using it.

• If your rows are a filterable set of accounts, use the bridge. It got most of the way there, it stays a native visual, and a Vega spec your colleague cannot edit is a real maintenance cost.
• A Deneb visual does not cross filter out to the rest of the page the way a native one does. If the statement is meant to drive the other visuals, that changes the design.
• You are reimplementing what the matrix gave you free. Column headers, row order, subtotal emphasis and every number format. That is genuine work, and it is why the template matters more than the idea does.

And it does not make the base measures faster. It removes dispatch, per cell format strings and per cell conditional formatting. It does not make a SUM over 74.9 million rows quicker. On this model the floor is 33 ms. If your base measures cost 800 ms, 800 ms is what you get.

On a real report

The lab is a lab. The test is whether it survives a production model.

I took the method to a client matrix with the same shape: fifteen period columns from a calculation group, twenty-eight measures on the rows, dynamic format strings on twenty-one of them. In Desktop it was merely very slow. Published to the Service, it returned an error instead of rendering.

Rebuilt as a grid, it went from 10,984 ms to 406 ms, with no semantic model change and no new DAX. Not one measure, calculated column or calculation item was added or edited. The same ten base measures the matrix already used are the only things the new visual asks for.

It renders in the Service now.

That measurement was taken on the client’s own model rather than under the protocol used for the table below, so treat it as a field result rather than a row in the same table.

Every build, side by side

Nine implementations of the same financial statement, one file, one session. Performance Analyzer, Clear, then Refresh visuals, three passes each, median reported. Every page carries the same filter context: year selected, month, channel and category all clear.

Run to run variation was under ten per cent on every page.

Two comparisons in that table are worth more than the headline. The accounts statement goes from 4,978 ms to 407 ms on the bridge alone, with no custom visual involved. And the two calculation group build is slower than the SWITCH it was meant to modernise, by a factor of five.

Get the file

The project with all nine builds in one report, the Deneb grid template, the generators and the tie out queries:

PL Switch Lab, the demo file and how to run it github.com/InsightfulAnalytics/PBI_Agentic_Dev

It also ships as an agentic development skill, performant-matrix, which builds the grid against your own model’s measures rather than against the lab’s. The row registry, the format rules and the tie out all have to match your measures, which is exactly the kind of precise, model specific work that is worth handing to an agent working from a template that already knows where the traps are.

The performant-matrix skill github.com/InsightfulAnalytics/PBI_Agentic_Dev

Both live in PBI_Agentic_Dev, a set of Power BI and Fabric skills for coding agents.

Reproducing the failure

The error at the top of this article is not a screenshot of a broken file, and it is not something that only happens once a report is published. Power BI Desktop will apply a capacity’s limits for you if the correct option is selected.

Under File, Options and settings, Options, Current file, Report settings there is a section called Query-limit simulations. Choose Shared capacity and Desktop enforces the same ceilings a published report meets: 1 GB of memory per query, and 225 seconds.

Set it, open the monthly calculation group page, and the matrix stops rendering and starts refusing. Set it back to no query limits and it renders in about eleven seconds. Both readings are worth taking on the same file, because the distance between them is the distance between a report that is irritating and a report that is broken.

It is also the honest way to check your own work. A statement that is merely slow on your machine can be one that a shared capacity declines outright, and this setting is the cheapest way to find that out before your users do.

The file ships with no query limits, so every number in this article reproduces as written. The setting lives under Current file, so it travels with the project.

Try it yourself

Here is the rebuilt statement running live in the Power BI Service. It is the same grid the 206 ms reading came from, over the same 74.9 million row model.

pl-bridge-demo.pbix