Why Good Analytics Depends on Knowing What Not to Compute Too Early

Apr 24, 2026

9 min read

5 views

71%

The hidden question behind every data model

What if the biggest performance problem in analytics is not the amount of data, but when you decide to compute it?

That question sounds technical, but it reaches into a deeper design principle: every analytical system has to choose between doing work early and doing work late. Some results should be baked into the data itself, stored once and reused forever. Others should remain fluid, calculated only when someone asks a question. The quality of a model often depends less on whether it can answer a question, and more on whether it answers it at the right moment.

This is the real tension connecting row-level calculations and set-based operations. One lives in the world of materialized values, where each row carries its own computed truth. The other lives in the world of deferred evaluation, where meaning emerges only after multiple queries are combined, filtered, and sorted. In both cases, the central issue is the same: what belongs inside the data, and what belongs in the query?

The temptation to store everything

It is easy to understand why people keep adding calculated columns. They are intuitive. They feel concrete. If a business question is, “What is the margin on each order?” the instinct is to put margin directly onto every order row and move on.

That instinct works, but it carries a hidden cost. A calculated column is not just logic, it is permanent storage. Every row gets its own value, and the file gets bigger. The refresh gets slower. The model becomes heavier. What looked like a small convenience becomes a long-term tax.

This is true in spreadsheets, databases, and BI tools alike. We love answers that feel tangible, so we materialize them. But the more often we store an answer, the more we confuse representation with reasoning. A model that stores too many answers stops being a model and starts being a warehouse of assumptions.

Think of a calculated column like printing a shipping label for every package before you know where the delivery route will go. You may save a tiny bit of effort at dispatch time, but you pay for it in paper, storage, and rigidity. If the route changes, every label may need to be reconsidered.

The first rule of efficient analytics is not “compute less.” It is “compute at the lowest level that preserves flexibility.”

That phrase matters because row-level calculations and aggregate calculations are not just different formulas. They reflect different philosophies of truth. A row-level value says, “This fact belongs to this record.” A measure says, “This answer depends on the question being asked.”

Why context is not a detail, but the engine

Measures are often introduced as a performance feature, but that is only part of the story. Their deeper power comes from filter context: the idea that the meaning of a number changes depending on which slice of reality you are looking at.

Total sales is not one number. It is many possible numbers. Total sales by year, by employee, by product, by region, by channel, by time window, each one is the same measure under a different context. The measure stays stable, but the question changes. That is why a measure is more than a shortcut. It is a way to let the model remain responsive to the user’s intent.

This is where many analytical systems go wrong. They precompute too aggressively, trying to anticipate every future question. But anticipatory computation is expensive because it assumes the future will resemble the present. In practice, users rarely ask for one fixed answer. They explore. They slice. They compare. They change the frame. A good model does not force them to inherit yesterday’s assumptions.

A simple example makes this obvious. Suppose you create a calculated column for “sales per customer” by dividing total sales by customer count at the row level. That may look tidy, but it can distort the meaning of the result. If later you want sales per customer by month, the precomputed row value may no longer reflect the correct grouping. A measure, by contrast, recalculates inside the current filter context and stays faithful to the question.

The lesson extends beyond Power BI. In any analytical environment, context is the real computation layer. The numbers are just outputs. The important work is deciding the scope under which those outputs are valid.

Set operators reveal the same law at a different scale

At first glance, combining SELECT statements with UNION or similar operators seems unrelated to calculated columns and measures. But the same deep principle is hiding there too: meaning emerges only when structures are aligned properly.

You cannot combine queries arbitrarily. Each SELECT must have the same number of columns, and corresponding columns must align by datatype. Only then can the database treat them as compatible pieces of one larger result. The ORDER BY and LIMIT clauses appear only after the final SELECT, because sorting and truncating belong to the result set as a whole, not to its individual fragments.

This is not just a syntax rule. It is a statement about how relational thinking works. Before you can compare, merge, or sort, you must make entities commensurable. Data has to speak the same structural language.

Imagine trying to merge two spreadsheets where one has columns for product, region, and revenue, while the other has customer, date, and quantity. You can force them into a single table, but unless you align the shape first, the merged result is nonsense. The database’s constraints are simply a formal version of a deeper truth: aggregation depends on compatibility.

That truth mirrors the measure versus calculated column distinction. A calculated column says, “I can evaluate this row on its own.” A compound select says, “I can only combine these queries if they share a common shape.” In both cases, the system is asking the same question: what is the unit of meaning here, a row, a query, or an entire result set?

The most important design work in analytics is not calculation. It is alignment.

That word deserves emphasis. Alignment means that values, filters, and query fragments all live in the right layer. If a number depends on context, leave it for a measure. If a result depends on combining compatible subresults, let the compound query do the work. If a rule depends on every row independently, then a calculated column may be justified. The art is knowing which layer owns the truth.

A mental model: the three homes of logic

A useful way to think about analytics is to divide logic into three homes.

1. The source home

This is where data is first created or shaped, such as a database view or source query. Logic here is best when it is foundational, stable, and universally useful. If every downstream system needs the same derived field, this is often the cleanest place to compute it.

2. The model home

This is where data is loaded and stored in the analytical layer. Calculated columns live here. They are useful when the computation is inherently row-based and must travel with the data. But every formula placed here increases model weight, so this home should be used sparingly.

3. The question home

This is where measures and compound query logic live. Here, calculations depend on the current filter context, the current slice, or the current combination of result sets. This is the most flexible home because it answers questions, not just records facts.

This framework is powerful because it replaces a vague rule like “use measures when possible” with a more precise test:

Does the logic belong to each individual row?
Does it belong to the shape of the data before storage?
Or does it belong to the question being asked right now?

Once you ask that, many modeling decisions become clearer. A discount flag may belong in the source if it is a stable business rule. A row-level classification may belong in the model if it needs interactive use and cannot be expressed upstream. But total sales by year should almost always remain a measure, because year is not a property of the row. It is a lens applied to the row.

The same framework also clarifies query composition. When you union two datasets, you are not just stacking rows. You are asserting that they occupy the same semantic shape. You are saying the result set has a single home. If that shape is wrong, the result is not merely inefficient. It is conceptually broken.

The real tradeoff is rigidity versus responsiveness

The deeper pattern across both topics is not storage versus computation. It is rigidity versus responsiveness.

A calculated column is rigid because it freezes a judgment into every row. That can be helpful when the judgment should never change. But if the business question evolves, the stored value may become a liability. A measure is responsive because it recalculates as the user changes filters. It is less concrete, but more alive.

Likewise, a compound select is powerful because it lets multiple query fragments behave as one, but only if they are structurally aligned. That alignment makes the result coherent enough to sort, limit, and analyze. Without it, you have fragments instead of a usable answer.

The best analytical systems therefore do not maximize one style of logic. They distribute logic according to volatility. Stable rules get pushed earlier. Variable interpretations get deferred later. Reusable shape constraints get enforced at the query boundary. This is the difference between a model that merely stores data and a model that can genuinely think with data.

A practical corollary follows: whenever you are tempted to encode a number permanently, ask whether the number is a fact or an interpretation. Facts are relatively durable. Interpretations change with context. The more your business question depends on comparison, grouping, or slicing, the more likely you need deferred calculation rather than stored computation.

Key Takeaways

Ask where the truth lives. Before creating a calculated column, decide whether the logic belongs to the row, the data model, or the current query context.
Prefer deferred calculation for flexible questions. If the number needs to change by year, product, region, or any other filter, a measure is usually the better home.
Treat storage as a cost, not a default. Every calculated column increases file size and can slow refresh, so use them only when row-level persistence is genuinely valuable.
Respect structural alignment in query composition. When combining SELECT statements, make sure the column count and datatypes line up, because compatibility is what makes the result meaningful.
Design for volatility. Stable logic can be materialized earlier, but questions that depend on changing context should stay dynamic as long as possible.

Conclusion: analytics is not about putting numbers somewhere, but about putting them in the right place

The most important shift in analytical thinking is to stop asking, “Can I compute this?” and start asking, “Where should this computation live?” That question changes everything.

A calculated column is not just a formula. It is a commitment to permanence. A measure is not just a calculation. It is a commitment to context. A compound select is not just syntax. It is a commitment to structural compatibility. Together, these ideas point to a single discipline: good analytics is the art of placing logic at the right level of abstraction.

Once you see that, you stop treating the data model like a dumping ground for convenient answers. Instead, you see it as an architecture of responsibility, where some truths are stored, some are deferred, and some are only valid when separate pieces have been properly aligned. That is not just a performance strategy. It is a more intelligent way to think.

In the end, the best analytical systems are not the ones that know the most. They are the ones that know what not to decide too early.