Dataverse Formula Column: Complete Guide [With Real Examples]

If you’ve recently tried creating a new Calculated Column in Dataverse, you may have already seen a yellow warning banner that says something like: “The Calculated behavior will eventually be discontinued.”

That’s Microsoft telling you it’s time to move on. Formula Columns are the replacement — and honestly, once you get familiar with them, they’re better. They use Power Fx (the same language you already know from Canvas Apps), they update in real time, and they work across all your Dataverse endpoints: Canvas Apps, Model-Driven Apps, Power Automate, Power BI, and the Dataverse API.

In this guide, I’ll walk you through everything you need to know — what formula columns are, how they’re different from calculated columns, how to create one step by step, and several real-world examples you can adapt for your own projects. I’ll also cover the limitations honestly, because there are a few important ones you need to know before you build something and hit a wall.

What is a Dataverse Formula Column?

A formula column in Dataverse is a column whose value is automatically calculated using a Power Fx expression. You define the formula once, and Dataverse calculates the result dynamically whenever the record is read — you don’t need to trigger anything manually.

Think of it like a calculated column in Excel, but built directly into your data table. The result is available everywhere: your Canvas App, your Model-Driven App views, Power Automate flows, Power BI reports, and any API call you make against the table.

Formula columns are now generally available (GA) — they’re no longer in preview. If your article or any documentation you’re reading still says “Preview,” it’s outdated.

Formula Column vs. Calculated Column — What’s the Difference?

This is the question I get asked the most right now, especially since Microsoft started showing the deprecation notice inside the Calculated Column editor. Here’s the honest comparison:

Feature	Formula Column	Calculated Column
Language	Power Fx	Legacy expression editor
Status	Generally Available (GA)	Being deprecated
Real-time update	Yes	Yes
Works in Canvas Apps	Yes	Yes
Works in Model-Driven Apps	Yes	Yes
Mobile offline support	No	Yes
Trigger workflows/plugins	No	No
Reference related tables	Yes (with sorting limits)	Yes
Currency data type support	Yes (function types)	Yes
Recommended by Microsoft	✅ Yes	❌ No — migrate away

Bottom line: If you’re building something new, always use Formula Columns. If you have existing Calculated Columns, plan to migrate them. Microsoft hasn’t given a hard end-of-life date yet, but the warning banner is there for a reason — the direction is clear.

Supported Data Types in Formula Columns

When you create a formula column, the result of your formula needs to match one of these output data types:

• Text — for string results, concatenation, or conditional text messages
• Decimal Number — for numeric calculations like totals, percentages, or ratios
• Yes/No (Boolean) — for true/false logic
• Date and Time — for date calculations

One thing that trips people up: Currency is not a supported output type for formula columns. If you need to display a currency-formatted result, you’ll need to use a Decimal and handle the formatting in your app layer.

Operators You Can Use

Inside your Power Fx formula, you can use these operators:

• + — Addition
• - — Subtraction
• * — Multiplication
• / — Division
• % — Percentage
• ^ — Exponentiation (power of)
• & — String concatenation (also works as AND in some contexts)
• in — Check if a value exists in a collection
• exactin — Case-sensitive version of in

Supported Functions (Full List)

Formula columns support a solid library of Power Fx scalar functions. Here’s everything available:

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

One important note about Text() and Value(): These functions only work correctly with whole numbers in formula columns — not decimals. Formula columns are evaluated without locale information, which means there’s no reliable way to handle decimal separators (like a comma vs. a period) across regions. If you need to convert a decimal number to text, you may need to work around this limitation.

Create a Dataverse Formula Column [Step by Step]

I’ll use a “Product Sales” table as an example here — it has columns for Name, Product ID, Quantity, and Sales (Price).

Step 1: Open your Dataverse table

Go to make.powerapps.com → Dataverse → Tables → open your table.

Step 2: Add a new column

Click + New column in the top toolbar. This opens the column panel on the right side.

Step 3: Fill in the column details

• Display Name — Give it a meaningful name. For example: Product Name And ID
• Description — Optional, but worth filling in so others understand what the column is for
• Data type — Click the dropdown and select Formula

Once you select Formula, a Formula (Fx) input box appears below.

Step 4: Enter your Power Fx formula

Type your formula in the Fx box. The editor gives you IntelliSense suggestions as you type — it recognizes your column names automatically.

Concatenate('Product Name' & "---" & 'Product ID')

Step 5: Set the Format

Depending on what your formula returns, set the Format. For example:

• Text output → select Text
• Numeric result → select Decimal Number

Step 6: Save the column

Click Save. The column is added to your table. Refresh the table view and you’ll see the formula results populated for your existing records.

Three Ways to Concatenate Fields in Dataverse (Real Example)

Let’s say I want to display both the Product Name and Product ID together in a single column. Here are three valid approaches — all produce the same result:

Approach 1 — Using Concatenate() with &:

Concatenate(Name & "---" & 'Product ID')

Approach 2 — Using Concatenate() without &:

Concatenate(Name, "---", 'Product ID')

Approach 3 — Using only the & operator:

Name & "---" & 'Product ID'

I personally prefer Approach 3 — it’s the cleanest to read, especially when you have more than two fields to join. Set the Format to Text and save.

Dataverse Formula Column Examples

Here, I explain 5 real-world examples you can use to create and test. You can also modify based on your requirements.

Example 1: Show a Discount Message Based on Sale Amount

Scenario: I want a column that shows “Discount Applicable” if the sale amount is over 500,000, and “Discount Not Applicable” otherwise.

Formula:

If(Sales > 500000, "Discount Applicable", "Discount Not Applicable")

Set the Format to Text. After saving, every record in the table will show the appropriate message based on the Sales value.

Where this is useful: Displaying eligibility flags directly in a Model-Driven App view, without needing any app-side logic.

Example 2: Calculate Total Amount With a Volume Discount

Scenario: I want to automatically calculate the total order amount. If the customer orders more than 3 units, they get a 50% discount. Otherwise, they pay full price.

Formula:

If(Quantity > 3, Quantity * Sales * 0.50, Quantity * Sales)

Where Quantity and Sales are both number columns in the table. Set the Format to Decimal Number.

Where this is useful: Sales tables, order management, or any scenario where you want the calculated amount visible in views and flows without storing it as a separate editable field.

Example 3: Calculate the Number of Days Since a Record Was Created

Scenario: I want to know how many days old each record is — useful for aging reports or SLA tracking.

Formula:

DateDiff(DateCreated, UTCToday(), Days)

Where DateCreated is a Date column on your table. Set the Format to Decimal Number.

This is one I’ve used in support ticket tables to surface aging directly in the view. No Power Automate flow needed, no calculated field to maintain — it just works.

Example 4: Normalize Text to Uppercase

Scenario: You want a display column that always shows a name in uppercase regardless of how it was entered.

Formula:

Upper(Name)

Clean and simple. Use this anywhere you need consistent display formatting — part numbers, codes, or category labels.

Example 5: Conditional Status Label Using Switch

Scenario: You have a numeric priority field (1, 2, 3) and want to display a human-readable label.

Formula:

Switch(Priority, 1, "High", 2, "Medium", 3, "Low", "Unknown")

This is much cleaner than nesting multiple If() statements, and it’s easier to maintain when you add new values later.

Known Limitations You Need to Be Aware Of

This is the section I wish existed in more guides — because hitting these unexpectedly in a live project is painful. Here’s what formula columns cannot do:

• No mobile offline support — Formula column values do not display when a Model-Driven App is running in offline mode. If your users work in the field without a connection, plan around this.
• Cannot trigger workflows or plugins — You can’t set up a workflow or plugin that fires when a formula column value changes. Formula columns are read-only computations.
• Maximum formula length is 1,000 characters — This sounds like a lot, but long If() chains or formulas using full column logical names can hit this faster than you’d expect. When importing solutions, the limit is applied using the full logical name of referenced columns, which is longer than the display name. If you have a complex conditional, switch to Switch() instead.
• Maximum column depth is 10 — A formula column can reference another formula column, which can reference another, and so on — but only up to 10 levels deep.
• Sorting disabled in certain scenarios — In Model-Driven App views, sorting is disabled on formula columns that reference related table columns, logical columns (like address fields), other formula/calculated columns, or time-bound functions like UTCNow().
• Currency output type not supported — You can use currency columns as inputs in your formula, but the output data type cannot be Currency.
• Don’t mix formula and calculated columns — Microsoft specifically recommends not referencing calculated columns inside formula columns or vice versa. It can cause unpredictable behavior.
• Duplicate detection rules don’t apply — If you have duplicate detection configured on your table, formula columns are excluded from those rules.
• String format restrictions — Columns of type String with format Email, Text Area, Ticker Symbol, or URL cannot be used in formula columns.
• Whole Number format restrictions — Columns of type Whole Number with format Language, Duration, or Time Zone aren’t supported.

Common Errors and Fixes

Check these five common errors while creating a Dataverse Formula Column that you may face.

Error: Dataverse Formula column shows blank in the app

Most likely cause: You’re testing in mobile offline mode. Formula columns don’t display values offline. Switch to online mode, and the values will appear.

Error: Dataverse Solution import fails because formula is too long

Cause: The 1,000-character limit is calculated using the full logical column names (e.g., cr123_productid instead of Product ID) when the solution is packaged. A formula that validates fine in the UI can fail on import.
Fix: Shorten your formula. Replace nested If() chains with Switch(). Break logic across multiple formula columns if needed.

Error: Column not available in the formula editor

Cause: You’re trying to use a column type that isn’t supported — like a String column with Email format, or a Whole Number with Duration format.
Fix: Check the supported types section above. You may need to use a different column or restructure your data.

Error: Sorting on the formula column is greyed out in views

Cause: The formula references a related table column or another formula column.
Fix: This is a known limitation. Sorting on this column won’t be available in Model-Driven App views. If sorting is critical, consider denormalizing the data into a regular column using a Power Automate flow instead.

Error: Text() function returning wrong decimal format

Cause: Formula columns don’t carry locale information. The Text() and Value() functions only work reliably with whole numbers.
Fix: If you need a decimal displayed as text, handle formatting in the app layer (Canvas App formula or Model-Driven App column formatting) rather than in the formula column itself.

Also, you may like some more Power Apps and Dataverse tutorials:

• Dataflow in Dataverse
• Power Apps Dataverse vs SharePoint List
• Dataverse Image Column
• Create Dataverse File Field
• Remove Commas From Dataverse Number Field

FAQ