Evaluate choice column/ option set using Fx expression in dataverse Formula column

(0) Share

Report

Posted on by sryzm

Hi,

Here is my scenario in a model driven app with a Dataverse table.

There is a Choice column in Table A called FoodType. User has the option to pick one of three choices: Fruit, Fruit & Veggies, Veggies from the global choice/optionset called FoodTypeCategoryOptions. The corresponding number values are 1001,1002,1003.

There is second column - a formula column (preview feature) on the same table A named FoodAction, in which I want to implement the following example logic:

If( 'Food Type'= 1001, "F", 'Food Type'= 1002, "FV", 'Food Type'= 1003, "V")

Receiving the following error on the evaluative portion of a simple expression like: If('FoodType'= 1001, "F", "Empty")

Incompatible types for comparison. These types can't be compared :
OptionSetValue(global_cr123_foodtypecategoryoptions), Value.

Note: cr123 is the publisher in this solution.

Need help with the appropriate syntax for extracting and referencing the selected Value and Text values, stored in the FoodType column. I can then use the true/false result in second part of an IF or Switch statement.

It is probably something obvious, but I can't find the right answer in google searches and chatGPT.

thanks in advance!

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

v-liwei-msft on at

Like (0)

Report

Hi @yozu

Unfortunately, this preview column doesn't support this data type now.

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/formula-columns#data-types

Hope it saves your time.

You can create a text column and then update the column through PowerApps or Flow.

Best Regards,

Levi

Was this reply helpful? Yes No
sryzm 6 on at

Like (0)

Report

Thanks. I hope this capability is on the roadmap.

Was this reply helpful? Yes No
Suggested answer

CU04100730-0 2 on at

Like (1)

Report

I have tested these 3 different formulas and they all seem to work. You need to refer to the choice values using the choice display name followed by the value.

Go to your Choices and check the display name. My choice display name is chProductVisibility, my values are Private, Public and Invite.

In my table, I have created a formula to check the value on Product Visibility Column ('Product Visibility')

1st test column created was TestChoicePrivate Column - result returned Yes or No

'Product Visibility' = chProductVisibility.Private

2nd test column created used IF - column name TestChoice

If('Product Visibility' = chProductVisibility.Private, "PR",

If('Product Visibility' = chProductVisibility.Public, "PU",

If('Product Visibility' = chProductVisibility.Invite, "IN", "")
)
)

3rd test column created used SWITCH - column name TestChoiceSwitch

Switch(

'Product Visibility',

  chProductVisibility.Invite, "IN",

  chProductVisibility.Private, "PR",

  chProductVisibility.Public, "PU"

)

I hope this helps!

Melina Louzada

Access Analytic

www.accessanalytic.com.au

Was this reply helpful? Yes No
Suggested answer

ankit_singhal 590 Super User 2025 Season 2 on at

Like (0)

Report

Please check below blog
https://www.matthewdevaney.com/25-power-apps-if-function-examples/#IF-Choice-Column-Value-Equals

Note: Mark verified, if it useful for you

Was this reply helpful? Yes No
Suggested answer

Richard Mamabolo 2 on at

Like (2)

Report

Syntax should look like: @FoodType = [@FoodType].'Fruit'

In order to achieve this perform the following actions:

1. In the Fx Formula field, type out the optionset name

2. Type out the equals sign

3. Type out the optionset name followed by Ctrl + Spacebar (this will display the [@FoodType] option)

4. Add a period after [@FoodType] like so: [@FoodType]. (This will show all the optionset names associated with the option set i.e Fruits, Fruits &Veggies and Veggies)

5. Select the relevant one. Done.

Was this reply helpful? Yes No