Dataverse Formula Column: How to Create One, If Examples, Errors and Limits

Last week the SPGuides IT lead asked me for two extra columns on our Asset table: a status label showing who has each device, and the days since we bought it. Nobody should type either value.

Typing them in a flow or a canvas app works until someone edits a row somewhere else. A Dataverse formula column solves that, because Dataverse computes the value from the row itself every time the row is read.

In this tutorial, we’ll create a dataverse formula column step by step, write If and DateDiff formulas, compare formula, calculated and rollup columns, and fix the choice-column error I hit.

Quick answer: In make.powerapps.com, open Tables > your table > + New > Column, set Data type to Formula, and type a Power Fx formula such as If('In Service', "Active - " & 'Assigned To', "Retired"). When the formula is valid, Format fills in (Text here). Select Save.

What Is a Dataverse Formula Column?

A formula column is a read-only column whose value comes from a Power Fx formula, the Excel-like language of canvas apps. If you’re new to tables, rows and columns, start with what Microsoft Dataverse is.

Dataverse does the math, so the same result shows up in model-driven apps, canvas apps, Power Automate, Power BI and the Web API.

The formula also decides the stored type. A text result becomes a Text column; a number becomes Decimal by default. You can’t change that type later, only formulas that keep it.

Formula vs Calculated vs Rollup Columns

Dataverse has two older computed types. Here’s how they compare, based on Microsoft Learn:

	Formula	Calculated	Rollup
Language	Power Fx (If, DateDiff, Switch…)	CONDITION / ACTION editor (ADDDAYS, DIFFINDAYS, CONCAT…)	SUM, COUNT, MIN, MAX, AVG
Data	Same row, related rows, other formula columns	Current table + parent table	Related child rows (1:N)
Updates	When the row is read	When the row is read	System jobs (hourly by default) or Recalculate
Key limit	1,000 characters, depth 10	Max 5 chained	200 per environment, 50 per table

For new work I use formula columns. They need no solution editor, and the formula reads like a SharePoint calculated column.

How to Create a Formula Column in Dataverse

I built this on the Asset table (11 rows) in my Power Platform developer environment. Columns include Asset Name, Category (choice), Purchase Date, Assigned To and In Service (Yes/No). Need a table first? See create a table in Microsoft Dataverse.

1. Go to make.powerapps.com and pick your environment at the top right.
2. Select Tables, open your table, then select + New > Column.

1. Type a Display name, for example Asset Status.
2. Open Data type, scroll down, and select Formula.

1. Type the formula in the Formula box. IntelliSense suggests column names as you type.
2. Check Format. It stays gray until the formula is valid, then fills in automatically.
3. Select Save. Each save took me about 15-20 seconds.

Formula Column If Statement Example

Here’s the Asset Status formula. It reads the In Service Yes/No column and joins text with &:

If('In Service', "Active - " & 'Assigned To', "Retired")

Dell Latitude 7450 (ASSET-1000) now shows “Active – Megan Bowen”, while Surface Dock 2 (In Service = No) shows “Retired”. A Yes/No column is already true or false, so it needs no comparison; our Dataverse Yes/No column guide covers it in canvas apps.

Days Since Purchase with DateDiff

For the age column, I subtracted the purchase date from today’s UTC date:

DateDiff('Purchase Date', UTCToday(), TimeUnit.Days)

On 10/22/2026, ASSET-1000 (bought 01/12/2026) returned 283 and ASSET-1007 (04/20/2026) returned 185. Dataverse saved the column as Decimal, the default for numbers. To hide decimals, set precision to 0 under Advanced options, or see remove commas from a Dataverse number field.

Learn doesn’t list Today(), so use UTCToday(). Formulas with UTCToday() or UTCNow() can’t feed a rollup column.

Supported Functions and Operators

Operators: + - * / % in exactin &. Functions, per the Learn formula reference:

Abs, And, Average, Blank, Char, Concatenate, DateAdd, DateDiff, Day, Decimal, EndsWith, Error, Exp, Float, Hour, If, IfError, Int, IsBlank, IsError, ISOWeekNum, IsUTCToday, Left, Len, Ln, Lower, Max, Mid, Min, Minute, Mod, Month, Not, Now, Or, Power, Replace, Right, Round, RoundDown, RoundUp, Second, Sqrt, StartsWith, Substitute, Sum, Switch, Text, Trim, TrimEnds, Trunc, Upper, UTCNow, UTCToday, Value, Weekday, WeekNum, Year.

There’s no LookUp or Filter, so a formula can’t search other tables. Output can be Text, Decimal, Whole Number, Float, Yes/No, Choice or Date and Time, but not Currency.

Common Errors and How to Fix Them

A choice column inside text. I tried a label that joins the asset name and its category:

'Asset Name' & " (" & Category & ")"

Category got a red underline, Save stayed disabled, and hovering showed:

This argument cannot be passed as type OptionSetValueType in formula columns.

A choice is an option value, not text. Compare it with the enum and return your own text instead (the Dataverse choices guide explains enums):

'Asset Name' & " (" & If(Category = 'Category (Assets)'.Laptop, "Laptop", "Other") & ")"

Saved as Asset Label, it returns “Dell Latitude 7450 (Laptop)” and “HP LaserJet Pro 4001 (Other)”:

Numbers inside text. Formula columns don’t convert numbers to text for you. Use Text with a format:

Text(123, "#") & " units"

Can’t change the formula. If a new formula returns another type, such as text instead of a number, create a new column.

Limitations to Know Before You Build

• Formulas max out at 1,000 characters, and chains of formula columns at 10 levels deep.
• Model-driven Dataverse views can’t sort a formula column that uses a related table, another formula or calculated column, or UTCNow().
• Values don’t show in mobile offline mode, and workflows, plug-ins and duplicate detection rules don’t fire on them.
• Email, Text Area, URL and Ticker Symbol text columns, and Duration, Language and Time Zone whole numbers, can’t be used.
• A blank number counts as 0, so a + b + c with a blank a still returns b + c. Calculated columns return blank instead.

Moving to test or production? Formula columns travel in a Dataverse solution like any column.

If you’re designing more of your Dataverse data model, these are good next reads:

• Power Apps Dataverse vs SharePoint List: Which One Should You Use?
• How to Create Dataverse File Field [With Examples]
• Dataflow in Dataverse [How to Create & Use]
• 51 Dataverse Interview Questions and Answers (With Scenarios)

Frequently Asked Questions