Do you struggle sometimes when introducing formula columns (fx Formula) on how to design/configure these? Now it is possible to use AI suggestions for formula columns to speed up the process and create your formulas quickly.
I will explain the AI suggestions for formula columns by using the following use case: on the Opportunity record there is a standard field for probability that can contain a value between 0 and 100. Since there is room for interpretation here of what that means, I would like to introduce a new column called Opportunity Chance which will have the logic in the table below to determine the value.
| Probability Range | Opportunity Chance |
| 0 – 40 | Unlikely |
| 41 – 74 | Likely |
| 75 – 100 | Very Likely |
Enable AI suggestions for formula columns
This feature is still in preview, so that is something to consider when enabling this feature. In order to enable this you need to do the following:
- Open Power Platform Admin Center
- Select the Environment where you want to enable this for
- Select Settings > Products > Features
- Find the AI suggestions for formula columns setting and switch this On
Using fx Formulas to determine Opportunity Chance
The following steps I have taken to complete the requirements for my use case:
- Open the Opportunity table and select > New Column
- Entered the Display name: Opportunity Chance
- For the Data type I select: fx Formula
- To get the formula suggestions, select the arrows and choose Get formula suggestions (Preview)
- I have entered the following prompt and I must honestly admit that it did take me a couple of attempts to get to this:
If the value of Probability is less than 41 then mark it as “Unlikely”, if the value of the Probability is greater than 40 and the value of probability is less than 75 then mark it as “Likely” and if the value of Probability is greater than 74 and the value of probability is less or equals 100 then mark it “Very Likely”
Reasons why this can be a bit challenging in the beginning:
- “Know your prompts”, what I mean by this is, that you do know how to make for example comparisons like greater than, less than, etc.
- Check/set values, what I mean by this is, how would you classify the “outcome” and should you use comma’s yes or no?
- The AI suggestions for the formula will be shown and you have the option to Copy the script
- When you navigate back by clicking the arrows again next to Get formula suggestions (Preview) and change this to Type a formula you can now paste the code generated by AI.
- Another limitation at the moment is that the fx Formula does not support choice type. So in our use case, I have selected Text as the Format of the field.
- Select Save and the column is created.
I have added the Probability and Opportunity Chance column to the form to test the functionality. When changing the probability, the opportunity chance field will now update accordingly
