Calculations in Power BI Desktop - Choosing Between Columns, Measures and Visual Calculations

The most common structural problem we find in client Power BI models isn't bad DAX. It's good DAX in the wrong place. A calculated column where a measure should be. A Power Query step doing work the model should do. A wall of measures recreating something a visual calculation handles in one line.

Power BI gives you at least five ways to compute a number: custom columns in Power Query, calculated columns in DAX, measures, calculated tables, and visual calculations. They all produce results that look identical on a chart. Under the hood they behave completely differently, and choosing wrong is the kind of mistake that doesn't hurt until your model hits a few million rows or your refresh window starts blowing out. Microsoft's documentation on calculations options in Power BI Desktop lays out what each one is. What it doesn't tell you is when to reach for which, so that's what this post covers.

The five options, quickly

Custom columns in Power Query are computed during data refresh, before anything reaches the model. They're written in M, not DAX, and the result is baked into the table like any other column.

Calculated columns are DAX expressions evaluated row by row when the model refreshes. They live in the model, consume memory, and behave like ordinary columns afterwards. You can slice by them, filter by them, put them on an axis.

Measures are DAX expressions evaluated at query time, in whatever filter context the visual provides. They consume no storage. They recalculate every time someone interacts with a report.

Calculated tables are whole tables generated by DAX at refresh time. Date tables are the classic case.

Visual calculations are DAX written directly on a specific visual, operating on the data already in that visual rather than on the model. Running totals, percent of parent, differences from the previous row. They arrived in preview in 2024 and are now solid enough that we use them on real client work.

Our default - it's a measure until proven otherwise

If a calculation aggregates anything, it's a measure. Full stop. Total sales, average margin, customer count, year-to-date anything. Measures respond to filter context, which is the entire point of Power BI. A measure for total sales works on a yearly chart, a monthly chart, a per-store table, and a single card, all without changing a character of code.

The anti-pattern we see constantly is a calculated column holding a pre-aggregated value, like a column on the customer table containing that customer's total revenue. It works, right up until someone filters the report to last quarter and the number doesn't change, because calculated columns are evaluated once at refresh and never again. The report quietly shows wrong numbers and nobody notices for months. We picked up exactly this issue during a model review for a Brisbane logistics client, where a "revenue per customer" column had been ignoring date filters for the better part of a year. Nobody had lied, the model had just been built by someone who didn't know the difference.

So when is a calculated column actually right? When you need the result as a thing to slice by. Price bands (Under $50, $50-$200, Over $200), age brackets, a flag for whether an order shipped late. Those are row-level classifications, they don't aggregate, and users want them on slicers and axes. Measures can't go on a slicer. That's the dividing line: if it belongs on an axis or a slicer, column; if it belongs in the values well, measure.

Push it upstream when you can

Here's the opinion part. If a calculation can be done in Power Query instead of as a DAX calculated column, do it in Power Query. And if it can be done in the source database or warehouse instead of Power Query, do it there.

The reasons are practical. Columns computed in Power Query compress better in the VertiPaq engine than DAX calculated columns, because they're present when the engine builds its compression dictionaries rather than bolted on afterwards. On a 50,000 row table you will never notice. On a 200 million row fact table, you will. We've seen model sizes drop 20 to 30 percent just from migrating a handful of DAX columns into the source view.

The second reason is reuse. A margin calculation living in a warehouse view is available to every report, every analyst, and every other tool in the business. The same calculation living as a DAX column in one PBIX file is available to that file. As organisations move onto Fabric and start treating their lakehouse as the single source of truth, this matters more, not less. It's one of the recurring themes in the modelling work our Microsoft Fabric consultants do: the best Power BI models are the ones where Power BI does the least computation.

The exception is anything needing model relationships or filter-aware logic. RELATED() lookups across tables, or columns referencing measures, have to stay in DAX. Fine. But "concatenate first name and last name" has no business being a DAX calculated column.

Visual calculations - genuinely good, with caveats

Visual calculations are the newest option and the one most people haven't tried. The pitch: instead of writing a measure with a pile of CALCULATE and time-intelligence gymnastics to get a running total, you write RUNNINGSUM([Sales Amount]) directly on the visual and it operates on the rows the visual already has.

For the specific class of problem they target, they're a big improvement. Running sums, moving averages, percent of parent total, rank within the visual, previous-row comparisons. The classic measure-based versions of these are fragile, because they have to anticipate the visual's structure. Change the chart's grouping and your carefully crafted measure breaks. A visual calculation just recalculates against whatever the visual now shows.

The caveats are real though. A visual calculation belongs to one visual, so if you need the same running total on four visuals you're writing it four times, and there's no central place to maintain it. They don't exist in the model, so they can't be referenced by measures, can't be picked up by Analyze in Excel, and won't show up in any external documentation tooling. Our rule with clients: visual calculations for presentation-layer arithmetic on a specific chart, measures for any number that's part of the business's shared vocabulary. "Revenue" is a measure. "Revenue as % of this chart's column total" can be a visual calculation.

One more honest warning. Because visual calculations are quick to write, report authors reach for them when the actual problem is a missing measure, and you end up with the same logic re-implemented slightly differently on six visuals. We've already cleaned this up once. The convenience cuts both ways.

Calculated tables and quick measures, briefly

Calculated tables earn their keep in exactly two places in most models we build: a proper date dimension (CALENDARAUTO or a custom DAX calendar) when the warehouse doesn't supply one, and occasionally a role-playing copy of a dimension. If you find yourself building calculated tables that join and filter fact data, that work belongs upstream, same argument as before.

Quick measures, the GUI that writes DAX for you, are a decent learning tool. Open the generated code and read it. They're not something we ship to clients, partly because the generated DAX is verbose, and partly because if nobody on the team can read the measure, nobody can fix it when it misbehaves.

How this plays out in practice

When we run a model health check for Power BI consulting clients, the calculation audit is usually the highest-value hour. The questions are always the same. Which calculated columns should be measures? Which should move to Power Query or the source? Which measures are duplicated with tiny variations because nobody knew one already existed? On one professional services firm's model we reduced 340 measures to about 90, and the refresh time halved once the heavyweight calculated columns moved into their warehouse views.

The pattern behind almost all of it is that calculation placement decisions get made one report at a time, by whoever is building that report that week, with no shared convention. None of the individual decisions are stupid. The accumulation is what kills you. A one-page convention document ("aggregations are measures, classifications are columns, transformations go upstream, visual calculations stay cosmetic") prevents most of it, and it's the kind of thing that pays off again when you start layering AI on top of your data. Copilot and the broader AI for business intelligence tooling work dramatically better against a clean semantic model with well-named measures than against a junk drawer.

The short version

Aggregating something? Measure. Need to slice or group by it? Calculated column, but push it to Power Query or the source if it doesn't need DAX. Building a date table? Calculated table is fine. Chart-specific arithmetic like running totals and percent-of-parent? Try a visual calculation before you write another fragile time-intelligence measure.

And if your model has hundreds of measures, columns nobody remembers creating, and refresh times that creep up every quarter, the problem probably isn't any single calculation. It's that nobody ever decided where calculations should live. That's fixable, and it's cheaper to fix now than after another year of reports gets built on top.