Calculated and rollup columns in Dataverse

Dataverse offers three related server-side computed-column types: calculated columns, rollup columns, and formula columns. They're easy to confuse and each has different trade-offs. Used correctly, they replace plug-ins and custom code with no-code declarations; used incorrectly, they slow records or produce stale data.

Calculated columns. A calculated column evaluates a formula at read time — every time the row is queried, the calculation runs. The formula can reference:

• Fields on the same record.
• Fields on related records (one level deep).
• Constants and simple arithmetic.
• Conditional logic (IF-style branching).
• Some date/text functions.

Example: a Total column on an Opportunity computed as Estimated Value * Probability. Every time the opportunity is loaded, the calculation runs.

Trade-offs.

• Always shows current values — no stale data.
• No storage cost — the value isn't persisted.
• Performance cost — every read recalculates.
• Can't be queried efficiently — filters on calculated columns don't use indexes.
• Limited formula language — not everything is expressible.

Rollup columns. A rollup column aggregates across related records — count, sum, average, min, max — and persists the result. Examples:

• Number of active opportunities on an Account.
• Total revenue of won opportunities on an Account.
• Latest activity date across all activities on a Contact.

Rollups refresh on a schedule (default: every hour) or on demand by clicking Refresh. The persisted value can be filtered and indexed.

Trade-offs.

• Can be queried and filtered efficiently.
• Limited to specific aggregations (count, sum, avg, min, max).
• Stale between refreshes — not real-time.
• Refresh job consumes platform resources at scale.
• Limited to one related-table aggregation per rollup.

Formula columns. Introduced more recently. Formula columns use Power Fx (the same language as canvas Power Apps) for richer formula syntax than calculated columns. They evaluate at read time like calculated columns, but with substantially more functions and constructs.

Trade-offs.

• More expressive than calculated columns.
• Real-time like calculated columns.
• Still read-time only.
• Cannot reference related-table aggregations (use rollups for that).
• Indexing is limited.

Choosing.

• Need real-time, simple arithmetic / conditional → calculated column or formula column.
• Need aggregation across related records → rollup column.
• Need real-time aggregation → use a plug-in or query-time computation, neither built-in column type works.
• Need to filter / sort on the value → rollup (which persists) or computed at integration time and stored to a regular column.

When to use a Power Automate flow instead. When the computation is non-trivial, involves multiple steps, requires updates triggered by changes, or needs to maintain a denormalised state — use a flow:

• Flow triggered on change of dependent fields.
• Compute the value.
• Update the target column.

This produces a stored value that can be queried efficiently with full real-time freshness. The trade-off is operational complexity — flows can fail, need monitoring, and don't backfill historical records automatically.

Plug-ins for complex cases. For computations needing complex logic, cross-table aggregation in real time, transactional consistency, or external API calls — a Dataverse plug-in is the right tool. Trade-off: substantial development effort vs the no-code options.

Common pitfalls.

• Filter / view performance — building views that filter on calculated columns produces slow queries at scale. Use rollup columns or stored values.
• Rollup staleness — using rollup values in real-time decision logic without forcing refresh produces wrong-feeling results.
• Formula column compatibility — exporting solutions with formula columns to older environments may fail; check target platform version.
• Calculated column cascades — calculated columns referencing other calculated columns chain at read time; complex chains slow record loads.

Operational discipline. Choose deliberately per use case. Default to formula columns or calculated columns for simple per-record calculations; use rollup columns for aggregations; reach for flows or plug-ins when neither built-in option fits. Document the choice and the formula for future maintainers.