Formula columns in Dataverse

Dataverse offers three flavours of derived columns: calculated, rollup, and formula. The formula column is the newest, using Power Fx (the same expression language as canvas apps) and evaluating at query time. Understanding when each fits avoids the common trap of using the wrong tool.

Quick comparison.

• Calculated columns — server-side formula, evaluated when reading the row. Limited to a subset of operators; classic since 2015.
• Rollup columns — aggregations over related child records (SUM, COUNT, etc.). Evaluated by a background job (every hour by default).
• Formula columns — Power Fx expression, evaluated at query time. Supports a richer expression language; introduced 2022.

Formula columns deeper dive. The Power Fx expression operates on the current row's columns and (limited) related-row columns. When the row is queried — through an API, a view, a form — the formula is evaluated and the value returned. The result is not stored; every read recomputes it.

Setup. In the maker portal:

1. New column → Data type: matches the formula's output (Text, Number, Decimal, DateTime, Yes/No, etc.).
2. Type the Power Fx expression in the formula box.
3. Power Fx provides Intellisense for table columns.
4. Save.

Supported Power Fx features.

• Arithmetic — + - * /, mathematical functions.
• Text operations — Concatenate, Left, Right, Mid, Upper, Lower, Trim.
• Logical — If, And, Or, Not, Switch.
• Date/time — DateAdd, DateDiff, Now, Today.
• Lookup field access — customerLookup.Name.
• Related entity columns — limited; lookup to parent's column allowed.

Not supported (or limited).

• Aggregations over child collections — Sum(Orders, Amount) doesn't work in formula columns; use rollup.
• Complex multi-level traversal — formula sees the current row and its direct lookups, not deep relationships.
• External data calls — no API calls in formulas.
• Recursion / loops — straightforward expressions only.
• Volatile functions — Now() works but is reevaluated each query; can affect caching.

Compared to calculated columns.

• Calculated uses a different, smaller expression syntax.
• Calculated can be used in Advanced Find filters; formula has limited filter support depending on output type.
• Calculated has stricter recalculation behaviour.

The trend: Microsoft is investing in formula columns, not calculated. New work should prefer formula unless a specific calculated capability is needed.

Compared to rollup columns.

• Rollup aggregates child records (number of related cases, sum of related order amounts).
• Formula doesn't aggregate over children — but can reference a single related lookup.

If you need "total order value per account," that's rollup. If you need "account display name = name + ' (' + city + ')'," that's formula.

Performance considerations.

• Formula columns are recomputed on every read. For high-volume queries, this adds cost. For small datasets or rare queries, it's negligible.
• Filtering on a formula column can prevent the database from using indexes — full table scans result.
• Sorting on a formula column has similar limitations.

For columns used heavily in queries and views, a stored calculated column (with traditional pre-computation) or a plugin that maintains a real column may outperform formula columns.

Dependencies. Formula columns reference other columns. If a referenced column is renamed or deleted, the formula breaks. Maker portal warns; the formula may need to be rewritten. Maintenance discipline: when changing a column referenced in formulas, audit all dependents first.

Use cases.

• Display names — concatenations, prefixed labels, status-friendly text.
• Date math — days until due, days since created.
• Categorisations — case priority based on type and severity logic.
• Currency display — combined currency code and amount.
• Computed flags — booleans based on row state.

Common pitfalls.

• Trying to aggregate over children. Formula doesn't support this; use rollup.
• Performance impact in large lists. Slow views, slow exports; consider whether the column needs to be a formula or could be stored.
• Circular reference attempts. Formula A references B, B references A — rejected at save time.
• Power Fx differences from canvas Power Fx. Some functions available in canvas apps aren't in formula columns; check the documentation when porting.
• Decimal precision. Formula output precision can differ from source; double-check rounding.

Operational guidance. Default to formula columns for new derived data needs. Reserve calculated columns for legacy maintenance or specific behaviour they exclusively support. Reserve rollup columns for parent-child aggregation. The clarity of using the right tool keeps the data model maintainable.