Complete Guide to Dataverse Formula Columns: Functions, Operators, and Parent Table References

Since calculated columns are being deprecated, I conducted a detailed investigation into formula columns as their replacement.

Formula Columns

Formula columns are a recently added data type in Dataverse columns that allow you to calculate column values using formulas.

Since calculated columns now show a “deprecated” message, I decided to thoroughly investigate these formula columns, which are likely to see increased usage in the future.

Note: To avoid confusion between “calculated columns” and “formula columns”, we’ll refer to “formula columns” simply as “formula columns” throughout this article.

Available Operators

First, these operators are available in formula columns:

For more details about operators, please refer to:

Operators and Identifiers in Power Apps - Power Platform

Reference information including syntax and examples for the Operators and Identifiers in Power Apps.

learn.microsoft.com

Available Data Types

Here are the data types available in formula columns:

As of February 2024, “Currency,” “Whole Number,” and “Choice (former Option Set)” are not supported. When handling numbers in formula columns, they are always treated as decimal numbers.

Available Functions

Here are key examples of functions available in formula columns:

Note: For a complete list of functions, please refer to:

Work with Dataverse formula columns - Power Apps

Learn how to create and use formula columns in Microsoft Dataverse.

learn.microsoft.com

Some functions have slightly different specifications compared to Power Apps canvas, as explained below.

Difference Between Now() and UTCNow()

In formula columns, Now() returns a datetime value in [User Local] time, while UTCNow() returns a datetime value in [Time Zone Independent] format. These are treated as different types of datetime data.

Therefore, (although unlikely to occur) if you try to use these two functions together, you’ll get an error as shown in the image:

Text and Value Functions Only Work with Integers

As of February 2024, the Text and Value functions can only be used with integer values, and cannot be used with decimal numbers or datetime values.

As shown in the image, attempting to pass a datetime value to the Text function results in an error:

Text Function Limitations

Unlike in Power Apps canvas, the Text function requires a second argument (format specification).

As shown here, passing only a number to the function results in an error:

You must specify the second argument (format):

Detailed Investigation

In addition to the basic usage of formula columns covered earlier, I investigated specific behaviors and operations.

Referencing Other Column Values

To reference your own column for calculations, simply enter the column name.

For example, when you want to reference and calculate using your column “Num”, the formula would look like this:

Referencing Parent Record Values

To reference parent record values, enter the parent table name to access it.

For example, when you want to use the “Num” column from the parent table “Parent”, the formula would look like this: Note: If the parent table name is a reserved word like “Parent” or contains spaces, enclose it in single quotes (”).

How Far Can Parent References Go?

Since parent table references are possible, it’s interesting to see how far up the parent table hierarchy we can reference.

The result showed that references from the GrandChild table all the way up to GrandParent were possible.

Considering that Power Fx in Power Apps canvas can only reference immediate parent properties, this functionality is quite valuable.

When Exactly is “Now” in Now()?

I tested to determine whether the Now() function in formula columns uses “Now() at the time of record creation” or “continuously calculated Now() results.”

The conclusion is that it uses “continuously calculated Now() results”, as shown by how the column values change depending on when you reference them.

Is Sorting Possible Using Formula Columns?

Since the traditional calculated columns had some sorting limitations, I tested various sorting capabilities with formula columns.
Note: Due to the length of this topic, I’ve covered it in a separate article:

Power Apps Dataverse: Complete Guide to Formula Column Sorting vs Calculated Column Sorting

I conducted a detailed investigation into the sorting (Sort) capabilities of Dataverse formula columns.Note: To avoid co...

ippu-biz.com

2025.02.22

Summary

Formula columns appear to be significantly more powerful compared to calculated columns.
Personally, I find it particularly beneficial that we can now reference distant parent tables and that (as far as I’ve investigated) sorting limitations have been removed.