What is Calculated on Demand in Power BI?

Building a Power BI report that’s fast and interactive hinges on how you handle your calculations. Instead of pre-calculating every possible number, Power BI often performs "calculations on demand" to keep your reports responsive. This article will break down exactly what that means, how it works using DAX measures, and why it’s the key to creating efficient and powerful dashboards.

What Does "Calculated on Demand" Truly Mean?

In simple terms, a calculation that is performed "on demand" is not pre-computed and stored physically in your data model. Instead, the calculation is run in real-time, exactly when it's needed. This happens whenever a user interacts with a report visual - like adding a metric to a chart, applying a filter, clicking on a slicer, or cross-filtering by selecting a bar in another chart.

Think of it like ordering food at a high-end restaurant. The chef doesn't have a bunch of pre-made meals sitting under a heat lamp. When you place your order, they cook it fresh, exactly to your specifications. In Power BI, your interactions are the "order," and the on-demand calculation is the "chef" preparing the result instantly based on your request.

The crucial concept here is the filter context. Every visual and filter on your report page creates a specific "view" of your data. An on-demand calculation looks at that specific context and computes a result that is relevant only to that view. If the context changes (e.g., you filter for a different year), the calculation runs again automatically, delivering an updated, accurate result.

DAX Measures: The Engine of On-Demand Calculations

The primary tool for creating on-demand calculations in Power BI is a DAX measure. DAX, which stands for Data Analysis Expressions, is the formula language of Power BI. While it might look a bit like Excel formulas, it's far more powerful, especially when it comes to on-demand analysis.

What is a DAX Measure?

A DAX measure is a formula that always returns a single, aggregated value (like a total, average, or count). The key thing to remember is that a measure doesn't store any data itself, it only stores the formula. The value you see is the result of that formula being executed within the current filter context.

Let's use a fundamental and straightforward example. Imagine you have a table named Sales with columns for Product, Units Sold, and Revenue. To calculate the total revenue, you would create a measure like this:

Total Revenue = SUM(Sales[Revenue])

How the Magic Happens: Measures in Action

The "on-demand" magic becomes clear when you see how this single measure behaves in different scenarios:

• On a Card Visual: If you drop the Total Revenue measure onto a card visual, the filter context is "the entire Sales table." The measure runs, summing up every value in the Revenue column to give you the grand total.
• In a Table Visual by Product: If you create a table visual with the Product column and your Total Revenue measure, the measure calculates separately for each row. For the "T-Shirt" row, the filter context is automatically "all sales where Product = T-Shirt." The measure runs and gives you the total revenue just for T-shirts. For the "Hats" row, it runs again in the context of "Hats," and so on.
• With a Slicer: Now, add a slicer for the "Year" column. If a user selects "2023," the filter context for all visuals on the page is updated to "only data from the year 2023." Your Total Revenue measure instantly recalculates everywhere it's used, showing the 2023 totals for each product and for the grand total card.

You wrote one formula, but it produces endless, context-aware answers without storing any of them. That's the power of on-demand calculation.

The Opposite Side: Pre-Calculated Columns

To fully appreciate "on-demand," it's essential to understand its counterpart: calculated columns. While also created using DAX, calculated columns work in a fundamentally different way.

A calculated column adds a new physical column to one of your tables. The formula for a calculated column is computed once for every single row in that table during the data refresh process. Once calculated, the results are stored in your data model, consuming memory and increasing your file size.

Calculated Columns vs. DAX Measures: A Quick Breakdown

Let's compare the two on the most important factors:

1. When They Are Calculated

• Calculated Column: Calculated during data refresh. The results are static until the next refresh.
• DAX Measure: Calculated on demand when a user interacts with a report. The results are dynamic.

2. How They Are Stored

• Calculated Column: Physically stored in memory (RAM). Each value in the column takes up space, potentially making your PBIX file very large.
• DAX Measure: The formula is stored, but the results are not. They are computed in the moment and take up very little space.

3. The "Context" They Understand

• Calculated Column: Operates in a row context. This means that as it calculates for a specific row, it can see the values of other columns in that same row. It has no awareness of filters on the report page.
• DAX Measure: Operates in a filter context. It sees the aggregated data based on all the filters applied by visuals, slicers, and user interactions.

When to Use One Over the Other

Imagine in your Sales table you have Revenue and Cost, and you want to find the profit for each sale. You could create a calculated column:

Profit = Sales[Revenue] - Sales[Cost]

This formula runs for every row, calculating and storing the profit for that specific transaction. It doesn't care what filters you have on your report. It is pre-calculated.

If you wanted total profit, you would use a measure:

Total Profit = SUM(Sales[Profit])

Or even smarter, create measures for total revenue and total cost first, then create a profit measure that references them:

Total Profit = [Total Revenue] - [Total Cost]

This Total Profit measure would calculate on demand, dynamically responding to any filters you apply.

Why On-Demand Calculation is a Game Changer

Understanding this distinction directly impacts the quality and performance of your reports.

Dynamic and Interactive Reports

On-demand calculations are the reason Power BI feels so fluid and responsive. They allow users to explore and analyze data from different angles, and the visuals update immediately. Without them, reports would be static and far less insightful.

Efficient Models and Smaller File Sizes

Since measures don't store large amounts of data, they keep your model lean and efficient. A common mistake for Power BI beginners is to create too many calculated columns where measures would have been a better choice. This can bloat the file size, slow down refresh times, and make the report sluggish.

Rule of thumb: Use measures by default. Only use a calculated column when you have a specific reason to do so.

Greater Flexibility

One measure can answer hundreds of questions. Your Total Revenue measure can show you revenue by year, by region, by salesperson, by day of the week, and any combination of those - all without writing a new formula. Calculated columns are not nearly as flexible, as their values are fixed at the row level.

So, When Should You Use a Calculated Column?

Despite the overwhelming benefits of measures, calculated columns are still very useful and necessary in certain situations. You should opt for a calculated column in these scenarios:

1. When You Need to Slice or Filter by the Result: You can't put a measure in a slicer, on an axis of a chart, or in the filter pane to filter other visuals. If you need to categorize your data for an analysis, a calculated column is the right tool. For example, you could create a "Deal Size" column that categorizes each sale as "Small," "Medium," or "Large" based on its revenue. You could then use this column in a slicer to filter your Total Revenue measure.

Deal Size = IF(Sales[Revenue] < 100, "Small", IF(Sales[Revenue] < 1000, "Medium", "Large"))

1. Performing Truly Row-by-Row Logic: When a calculation exclusively depends on the values in the current row and only that row, a calculated column is often more direct. The Profit = Sales[Revenue] - Sales[Cost] example from earlier is a perfect case.
2. Complex Calculations for Performance: In rare, advanced scenarios where a row-level calculation is computationally expensive, it can sometimes be better to pre-calculate it with a column at refresh time rather than force the measure to re-calculate it live with every user click. This decision requires performance testing and should not be your default approach.

Final Thoughts

Understanding on-demand calculations is a major step toward mastering Power BI. By defaulting to DAX measures, you create dynamic and efficient reports that respond instantly to user curiosity. While calculated columns hold a necessary role for categorizing and slicing data, it’s the on-demand engine of measures that breathes life into interactive analysis.

Delivering on-demand reports and real-time dashboards is the goal, but tools like Power BI come with a steep learning curve. At Graphed, we simplified the whole process. Instead of learning DAX, you can connect your analytics, sales, or marketing platforms and simply describe the dashboard you need in plain English. We instantly build and maintain real-time, interactive dashboards for you, turning hours of tedious report-building into a thirty-second conversation.