Work with formula columns (preview)

[This topic is pre-release documentation and is subject to change.]

Formula columns are columns that display a calculated value in a Microsoft Dataverse table. Formulas use Power Fx, a powerful but human-friendly programming language. Build a formula in a Dataverse formula column the same way you would build a formula in Microsoft Excel. As you type, Intellisense suggests functions and syntax, and even helps you fix errors.

Note

Formula columns can be added as a calculated field. Currently, formula columns can't be used in roll-up fields or with plugins.

Add a formula column

  1. Sign in to Power Apps at https://make.powerapps.com.
  2. Select Dataverse > Tables, and then select the table where you want to add a formula column.
  3. Select the Columns area, and then select New column.
  4. Enter a Display name for the column.
  5. Optionally, enter a Description of the column.
  6. In Data type, select fx Formula.
  7. Enter the formula in the Formula box.
  8. Select Save.

The following example creates a formula column called Total price. The Number of units column is a whole number data type. The Price column is a decimal data type.

Screenshot of a formula column definition.

The formula column displays the result of Price multiplied by Number of units.

Screenshot of a record with a formula column.

The formula that you enter determines the column type. You can't change a column type after the column is created. That means you can change the formula after you’ve created the column only if it doesn’t change the column type.

For example, the formula price * discount creates a column type of number. You can change price * discount to price * (discount + 10%) because that doesn’t change the column type. However, you can’t change price * discount to Text(price * discount) because that would require changing the column type to string.

Operators

You can use the following operators in a formula column:
+, -, *, /, %, ^, in, exactin, &

For more information, go to Operators in Power Apps.

Data types

You can display the following data types in a formula column:

  • Text
  • Decimal Number
  • Yes/No (boolean)
  • Date

The Currency data type isn't currently supported.

Function types

You can use the following function types in a formula column:

  • Decimal
  • String
  • Boolean
  • Option Set
  • DateTime (TZI)
  • DateTime (User local) (limited to comparisons with other user local values and the DateAdd function)
  • DateTime (Date only) (limited to comparisons with other date-only values, and the DateAdd function)
  • Currency
  • Whole Number, promoted to Decimal

Functions

You can use the following scalar functions in a formula column:

Abs

And

Day

Exp

If

Int

Len

Ln

Max

Mid

Min

Mod

Not

Or

Sum

Text *

Value *

* The Text and Value functions only work with whole numbers, where no decimal separator is involved. The decimal separator varies across locales. Since formula columns are evaluated without locale knowledge, there's no way to properly interpret or generate the decimal separator.

See also

Types of columns
Microsoft Power Fx overview