Error message: This argument cannot be passed as type OptionSetValueType in formula columns.

(1) Share

Report

Posted on by Inemesit Sifon

I am trying to calculate deductions (Custom Table) differently based on a contractor's contract type (Choice field) in a Model-Driven App using a formula column(PowerFx).

The field contractortype is a Choice (OptionSet) in the Custom Table called Deduction. We attempted to use formulas like Text(contractortype) or compare with values like "Internship", but we get errors such as:

“This argument cannot be passed as type OptionSetValueType in formula columns.”
“Incompatible types for comparison.”

The goal is to conditionally calculate values like:

RoundDown(strikes / 3, 0) * 0.25 * WiFiAllowance for Interns
RoundDown(strikes / 3, 0) * 2 for Paid Project

We're looking for an officially supported method to conditionally calculate values based on OptionSet (Choice) fields in formula columns.

We have tried multiple ways such as:
Switch(
    Text(contractortype),
    "Internship",
        RoundDown('Total Strikes' / 3, 0) * 0.25 * wifiallowance,
    "Paid Project",
        RoundDown('Total Strikes' / 3, 0) * 2,
    0
) // However we have errors such as: This argument cannot be passed as type OptionSetValueType in formula columns.

We also tried the below and got the error “'OptionSetValue' is an unknown or unsupported function.”:
Switch(
contractortype,
   OptionSetValue(920910000), // Internship
   RoundDown('Total Strikes' / 3, 0) * 0.25 * wifiallowance,

   OptionSetValue(920910001), // Paid Project
   RoundDown('Total Strikes' / 3, 0) * 2,

   0 // Default fallback value
)

Sample steps to recreating such situation
- Create a choice field when 2 options: Internship or Paid project
- Create a field for Total number of Strikes (This can be a whole number)
- create a total hour worked field (Whole number or decimal)
- Create a WIFI Allowance column (This can be a currency field)
- Create a Powerfx column to calculate that every 3 strikes equal a 2-hour deduction if the Choice field selected equals Paid Project, else 25% of the Wi-Fi allowance is deducted when the choice selected is Internship

This is a model driven app and same calculations work just fine on Canvas apps.

Any idea how i can get this to work?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

stampcoin 5,072 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@Inemesit Sifon Hi,

Please try use the value of contractortype , not the label.

for example,

Switch( true, // Internship Deduction.contractortype.Value = 920910000, RoundDown('Total Strikes' / 3, 0) * 0.25 * wifiAllowance, // Paid Project Deduction.contractortype.Value = 920910001, RoundDown('Total Strikes' / 3, 0) * 2, // fallback for any other type 0 )

Was this reply helpful? Yes No
Suggested answer

Gabriel G. 831 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi!

@stampcoin, the 'label' actually is the value of the option 'record' and is a unique value that you can use like the id. Sometimes, it is much more understandable if you use the values instead of ids, Power Apps take care of the reference ;).

@Inemesit Sifon You need to use the value of your choice column and not the choice type. An optionset is an array with records containing 2 fields: Id and Value. If you want to compare the value of the option, you have to use <optionsetcolumnname>.<valueOfOptionsetToCompareWith>. Same for id, <optionsetcolumnname>.<idOfOptionsetToCompareWith>

It should look like this (you will need to adapt this if column values differ):

Switch(

contractortype,

contractortype.Internship,

RoundDown('Total Strikes' / 3, 0) * 0.25 * wifiallowance,

contractortype.'Paid Project',

RoundDown('Total Strikes' / 3, 0) * 2,

0

)

I hope it helps!
_______________________________________________

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.

Was this reply helpful? Yes No
Inemesit Sifon 4 on at

Like (0)

Report
Copy link

Link copied!

Hello @GabGadou,

I appreciate the swift feedback. I tried this however, i got this error. I have tried so many formular scenerios i have lost count.

@stampcoin, Thank you to for the suggestion. I tried your suggestion once before and even now. I tweaked it even further but still end up with the same type of error.

I am not sure if it is related to MDA as this formulars would work effortlessly in canvas.

Was this reply helpful? Yes No
Suggested answer

Gabriel G. 831 Moderator on at

Like (0)

Report
Copy link

Link copied!

@Inemesit Sifon

the logic name you use is not the good one. Here's an example of a switch on my side: (use intellisense to help you 'ctrl+spacebar')

Switch(

'Status Reason'; (Column name inside the table)

'Status Reason (File Import Request Avoirs)'.Active; (Column name of the optionset + '.' + value to compare with)

"test1";

'Status Reason (File Import Request Avoirs)'.Inactive;

"test2"

)

Keep us informed!

Was this reply helpful? Yes No
stampcoin 5,072 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@Inemesit Sifon ,

This is realy annoying. When always gives me error if I tried to use the label. I have to use the schema name of the field.

Below is from the default Account table in dataverse, I added a new column ( the label doesn't always work).

since, it's dynamic for each record, ThisRecord.Column should be the one to be evaluated. in your case , it is the choice column

And after save, when I check the new column again, it is kind of @GabGadou used.

Was this reply helpful? Yes No