Work with formula columns

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.

Add a formula column

1. Sign in to Power Apps at https://make.powerapps.com.

2. Select Tables, and then select the table where you want to add a formula column. If the item isn’t in the side panel pane, select …More and then select the item you want.

3. Select the Columns area, and then select New column.

4. Enter the following information:

   • A Display name for the column.
   • Optionally, enter a Description of the column.

5. For Data type select fx Formula.

6. Type the formula or use formula suggestions:

   • Type a formula
   • Get formula suggestions (preview)

   Enter the Power Fx formula in the Formula box. More information: Type a formula

7. Select additional properties:
   • Select Searchable if you want this column to be available in views, charts, dashboards, and Advanced Find.
   • Advanced options:
     • If the formula evaluates to a decimal value, expand Advanced options to change the number of points of precision, between 0 and 10. The default value is 2.
8. Select Save.

Type a formula

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.

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

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 create the column only when 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.

Get formula suggestions (preview)

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

Describe what you want the formula to do and get AI generated results. Formula suggestions accept your natural language input to interpret and suggest a Power Fx formula using GPT-based AI model.

Prerequisites

To enable this feature, you must enable the AI suggestions for formula columns environment setting. More information: AI suggestions for formula columns

Example natural language input

Imagine there's a Customer rating column that shows their rating by account.

In the Get formula suggestions box enter the formula in natural language, such as If the rating on the rating column is equal or greater than 5 then indicate as Good and if less than 5 indicate as Average and if value is blank or zero then display as Bad, and then select the arrow button (enter).

Then copy the Suggested Formula.

And paste it into the Type a formula box. Select Save.

Here's how the formula appears when pasted.

Switch(
    ThisRecord.'Customer Rating',
    Blank(), "Bad",
    0, "Bad",
    1, "Average",
    2, "Average",
    3, "Average",
    4, "Average",
    5, "Good",
    6, "Good",
    7, "Good",
    8, "Good",
    9, "Good",
    10, "Good"
)

Check the computed Rating Description formula column, which appears like this.

Responsible AI

For information about responsible AI, go to these resources:

• FAQ for building apps and tables through conversation
• FAQ about using AI responsibly in Power Apps

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
• Whole Number
• Float
• Boolean Choice (Yes/No)
• Choice (formerly option sets)
• Datetime

More information: Create formula columns with decimal, whole number, float, and choice data types\

The currency data type isn't currently supported.

Function types

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

• Decimal
• String
• Boolean
• Choice
• DateTime (TZI)
• DateTime (User local) (limited to comparisons with other user local values DateAdd, and DateDiff functions)
• DateTime (Date only) (limited to comparisons with other date-only values, DateAdd, and DateDiff functions)
• Currency
• Whole Number

Functions

For the scalar functions you can use in a formula column, go to Formula reference - Dataverse formula columns.

* 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.

* StartOfWeek argument isn't supported for the WeekNum and Weekday functions in formula columns.

Function example

Guidelines and limitations

This section describes guidelines and the known limitations with formula columns in Dataverse.

Currency fields usage validations

• Formula columns don't support using a related table currency column in the formula, such as in this example.
• Direct use of currency columns and exchange rate in the formula is currently unsupported. The use of currency and exchange rate columns is achieved through the Decimal function, such as Decimal(currency column) or Decimal(exchange rate). The Decimal function makes sure the output is within the accepted range. If the currency or exchange rate column value exceeds the accepted range, then the formula returns null.
• Base currency columns aren't supported in the formula column expressions because they're system columns used for reporting purpose. If you want a similar result, you can use a currency column type along with an exchange rate column combination as CurrencyField_Base = (CurrencyField / ExchangeRate).

Date time columns usage validations

• Behavior of date time formula columns can only be updated when it isn't used in another formula column.
• For date time formula columns, while using the DateDiff function, make sure that:
  • User local behavior column can't be compared or used with a DateTime(TZI)/DateOnly behavior column.
  • User local behavior columns can only be compared or used with another user local behavior column.
  • DateTime(TZI) behavior columns can be compared or used in DateDiff functions with another DateTime(TZI)/DateOnly behavior column.
  • DateOnly behavior columns can be compared or used in DateDiff function with another DateTime(TZI)/DateOnly behavior column.
• Date time columns and date time functions UTCNow(), Now() can't be passed as a parameter to string functions.

Formula column usage in rollup fields

• A simple formula column is where the formula uses columns from the same record or uses hard coded values. For rollup columns, formula columns must be simple formula columns, such as this example rollup column.
• A formula column, which is dependent on time bound functions UTCNow() and UTCToday() can't be used in a rollup field.

Power Fx text function recommendations

• Formula columns don't support Text() functions with a single argument of type Number. Number can be whole, decimal, or currency.

• Formula columns don't support using numbers in the following configurations:

  • In string functions. These are string functions placed wherever a text argument is expected: Upper, Lower, Left, Right, Concatenate, Mid, Len, StartsWith, EndsWith, TrimEnds, Trim, Substitute, and Replace.
  • In the implicit formulas, such as 12 & "foo", or 12 & 34, or "foo" & 12.
  • Internal number to text coercion isn't supported. We recommend using Text(Number, Format) to convert a number to text. In the case where a String argument is passed in a Text function then the Format argument isn't supported.
  • Here's an example using the Text function to convert a number to text and append a string to it:

  Concatenate(Text(123,"#"),"ab")
  Text(123,"#") & "foo"
  

• Locale-specific formatting tokens such as "." and "," aren't supported in formula columns.

Range validations on formula columns

• You can't set the Minimum value or Maximum value properties of a formula column.
• All internal computations should lie within the Dataverse range for decimal type formula columns (-100000000000 to 100000000000).
• A hard coded literal value entered in the formula bar should lie within the Dataverse range.
• If there's a numeric column that's null then it's considered 0 in the intermediate operation. For example, a+b+c and If a = null, b=2, c=3 then formula column gives 0 + 2 + 3 = 5.
  • This behavior is different from calculated columns in this case because calculated columns give null + 2 + 3 = null.

General validations on formula columns

• Formula columns can reference other formula columns, but a formula column can't reference itself.
• Formula columns don't support cyclic chains, such as F1 = F2 + 10, F2 = F1 * 2.
• Maximum formula expression length in formula columns is 1,000 characters.
• The maximum depth allowed in formula columns is 10. Depth is defined as the chain of formula columns referring to other formula or rollup columns.
  • For example, table E1, F1 = 1*2, table E2, F2 - E1*2. In this example, the depth of F2 is 1.
• In model-driven apps, sorting is disabled on:
  • A formula column that contains a column of a related table.
  • A formula column that contains a logical column (for example, address column).
  • A formula column that contains another calculated or formula column.
  • A formula column that uses time-bound function UTCNow().
• Columns of type Whole Number with format Language, Duration, Time Zone aren't supported in formula columns.
• Columns of type String with format Email, Text Area, Ticker Symbol, URL aren't supported in formula columns.
• Formula columns don't display values when the app is in mobile offline mode.
• You can't trigger workflows or plug-ins on formula columns.
• We don't recommend using calculated columns in formula columns and vice versa.
• Duplicate detection rules aren't triggered on formula columns.
• The Now function can be used with formula columns. Now() has user local behavior and UTCNow() has time zone independent behavior.
• You can set the precision property for decimal columns.
• Default formula data type value is set to Decimal for numeric value returning formulas.
• Updating whole number formula column's format isn't supported.

Formula columns of data types that can't be produced

• Currency

See also

Types of columns

Microsoft Power Fx overview

Formula, calculated, and rollup columns using code