16.1 Calculated, Rollup, and Formula Columns

Microsoft Dataverse gives makers three no-code ways to store derived data — values computed from other data instead of typed by hand: calculated columns, rollup columns, and formula columns. The AB-410 exam tests whether you know which one computes when, and what each one cannot do.

Calculated Columns

A calculated column stores a value that Dataverse recomputes synchronously, in real time — whenever the row is saved or retrieved, the value is current. No background jobs, no waiting. You create one in the maker portal at make.powerapps.com: open a solution, select the table, add a New column, and in the Behavior dropdown choose Calculated. The option appears only for supported data types: Text, Choice, Yes/No, Whole Number, Decimal Number, Currency, and Date and Time.

The definition editor has two parts: an optional Condition section (branching logic with AND/OR operators) and an Action section (the formula). Expressions can use:

• Columns on the current table
• Columns on a related parent table through an N:1 lookup, using dot syntax such as ParentAccountId.AccountNumber

Built-in functions cover date math and text handling:

Classic scenarios: weighted revenue (Estimated Revenue multiplied by Probability), a follow-up date (ADDWEEKS based on priority), or days since creation (DIFFINDAYS).

Calculated column constraints to memorize

• One row plus one parent only — a calculated column spans at most two tables (current row and parent); it can never aggregate over child rows in a 1:N relationship
• A maximum of 5 chained calculated columns, and no cyclic references
• Saved queries, charts, and visualizations support a maximum of 50 unique calculated columns
• You cannot trigger workflows or plug-ins from a calculated column's value, and duplicate detection rules do not fire on it
• Calculated columns that reference a parent row's column or another calculated column lose search and sort capability — the exam expects awareness that these capabilities drop away once the expression leaves the current row
• You cannot convert an existing simple column into a calculated column; you must create a new column

Rollup Columns

A rollup column stores an aggregate over related rows: SUM, COUNT, MIN, MAX, or AVG computed across a 1:N relationship (parent down to children) or across a hierarchy (for example, an account plus all its child accounts). The definition editor has three sections: Source table (with an optional hierarchy), Related table (with filters, such as only open opportunities), and Aggregation.

The critical exam fact: rollups are asynchronous. Two recurring system jobs compute them, visible under Settings > Advanced settings > System Jobs > Recurring System Jobs:

Users can force an update from a form: the rollup column shows a calculator icon — select it and choose Recalculate. This online recalculation is capped at 50,000 related rows and a hierarchy depth of 10, works only in online mode, and requires Write access on the source row. You can also force recalculation programmatically with the CalculateRollupField function, for example from a cloud flow.

Every rollup column creates two hidden companion columns: <name>_date (last calculation time) and <name>_state (0 = NotCalculated, 1 = Calculated, plus error states such as OverflowError and RetryLimitExceeded).

Rollup constraints to memorize

• Defaults: a maximum of 200 rollup columns per environment and 50 per table, adjustable via the Organization table's MaxRollupFieldsPerOrg and MaxRollupFieldsPerEntity columns
• No workflow can be triggered by a rollup update, and workflow wait conditions cannot use rollup columns
• No rollup over another rollup, and a rollup cannot reference a calculated column that itself uses another calculated column
• Only 1:N relationships — never N:N — and you cannot roll up over the Activity or ActivityParty 1:N relationships
• Aggregation runs under the system user context: everyone sees the same value unless you restrict it with column-level security
• Exam trap: a rollup can be up to an hour stale. If logic needs the value the instant a child row changes, a rollup is the wrong tool

Formula Columns

A formula column evaluates a Power Fx expression — the same language used in canvas apps — in real time at query time. Nothing is stored; the value is computed when the row is read, so it is always current. Create one by choosing the fx Formula data type when adding a column at make.powerapps.com. With no system job and no save-time computation, formula columns are the lightest-weight option.

Key traits:

• The formula you type determines the column's data type, and the type cannot be changed afterward — you can edit the formula only in ways that keep the same return type
• Display types: Text, Decimal, Whole Number, Float, Boolean (Yes/No), Choice, and DateTime — Currency is not supported
• Operators include +, -, *, /, %, in, exactin, and &, with IntelliSense as you type
• Formula columns can be marked Searchable, making them usable in views, charts, dashboards, and Advanced Find
• Decimal results default to 2 points of precision (configurable from 0 to 10)

Current limitations worth knowing: the Text and Value functions work only with whole numbers (formula columns evaluate without locale knowledge, so decimal separators cannot be interpreted); the StartOfWeek argument is not supported in WeekNum and Weekday; and a formula cannot reference a related table's currency column.

Choosing Between the Three — and When a Column Is Not Enough

That last row is the bridge to the rest of this chapter: none of the three column types can do anything — they only compute values. When the requirement is an action (send an email, create a task, call an external API, run an approval, update another system), you need automation: a cloud flow, a classic workflow, or a plug-in. A common exam trap is answering with a column type when the scenario actually demands an action, or answering with a cloud flow when a formula column delivers the value with zero automation overhead.