- Article

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

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

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.Select the

**Columns**area, and then select**New column**.Enter the following information:

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

- A
For

**Data type**select.*fx*FormulaType 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

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

- If the formula evaluates to a decimal value, expand

- Select
- 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'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.

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

Important

This is a preview feature available only in US regions only.

Preview features aren’t meant for production use and may have restricted functionality. These features are available before an official release so that customers can get early access and provide feedback.

Currently, formula suggestions that reference a single table are supported. Formula suggestions that reference a column on a related table aren't supported.

#### 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
- Choice Yes/No (boolean)
- Datetime
- Whole Number

The currency, and choice (formerly option sets) data types aren'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 DateAdd, and DateDiff functions)
- DateTime (Date only) (limited to comparisons with other date-only values, DateAdd, and DateDiff functions)
- Currency
- Whole Number, promoted to Decimal

## 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

Description | Example |
---|---|

Retrieve a date value. | `DateAdd(UTCNow(),-1,TimeUnit.Years)` |

## Create a decimal formula column

Create a formula column that returns a decimal number.

- When you create a column, enter the following information:
- A
**Display name**for the column. - Optionally, enter a
**Description**of the column.

- A
- For
**Data type**select.*fx*Formula - Enter a formula that returns a numeric value in the
**Formula**bar.This example creates a formula column called*Total Amount*. The*Price Per Unit*column is of decimal data type. - Expand
**Advanced options**, select**Decimal**as the**Formula data type**, and then set the required number of decimal places. - Select
**Save**.

## Create a whole number formula column

Create a formula column that returns a whole number.

- When you create a column, enter the following information:
- A
**Display name**for the column. - Optionally, enter a
**Description**of the column.

- A
- For
**Data type**select.*fx*Formula - Enter a formula that returns a numeric value in the
**Formula**bar.This example creates a formula column called*Number of Units*.*Total Price*and*Price Per Unit*columns are of decimal data type. - Expand
**Advanced options**, and select**Whole number**as the**Formula data type**and set the required format for whole number column. - Select
**Save**.

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

- User local behavior column can't be compared or used with a
- 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`

.

- This behavior is different from calculated columns in this case because calculated columns give

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

- For example,
- 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.

### Power Fx functions not currently supported

- Power
- Sqrt
- Exp
- Ln
- ^ (operator)

### Formula columns of data types that can't be produced

- Choices (except Yes/No choice)
- Currency

## See also

Types of columns

Microsoft Power Fx overview

Formula, calculated, and rollup columns using code