Error: Expressions that yield variant data-type cannot be used to define calculated columns.

[Anonymous]

How do I solve this issue? I would like to create this Calculated column. 

CompletionStatus =

SWITCH(

TRUE();

TasksOpportunities[Status] = "Open" && TasksOpportunities[Days until Completion]<0;"Open";

TasksOpportunities[Status] = "Open" && TasksOpportunities[Days until Completion]>=0 && TasksOpportunities[Days until Completion]<7;"Open";

TasksOpportunities[Status] = "Open" && TasksOpportunities[Days until Completion]>7;"Standby";

TasksOpportunities[Status] = "Completed"; 0;

TasksOpportunities[Status] = "Cancelled"; 3;

BLANK()

)

[Status] = Text

[Days until Completion] = Whole number

[v-juanli-msft]

Hi @Anonymous 

Each result of the switch branch should be of same data type.

Try this instead

CompletionStatus =
SWITCH(
TRUE();
TasksOpportunities[Status] = "Open" && TasksOpportunities[Days until Completion]<0;"Open";
TasksOpportunities[Status] = "Open" && TasksOpportunities[Days until Completion]>=0 && TasksOpportunities[Days until Completion]<7;"Open";
TasksOpportunities[Status] = "Open" && TasksOpportunities[Days until Completion]>7;"Standby";
TasksOpportunities[Status] = "Completed"; "0";
TasksOpportunities[Status] = "Cancelled"; "3";
BLANK()
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Maggie,

Thanks for the explanation, this totally makes sense. 

Nevertheless I did get another error message when I tried your expression: 
A single value for column 'Status' in table 'TasksOpportunities' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Do you know why this is and how to solve it? 

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Do you use the formula in a measure as below instead of a column?

If so, please modify the formula as below to use in a measure

CompletionStatus =
SWITCH (
TRUE (),
MAX ( TasksOpportunities[Status] ) = "Open"
&& MAX ( TasksOpportunities[Days until Completion] ) < 0, "Open",
MAX ( TasksOpportunities[Status] ) = "Open"
&& MAX ( TasksOpportunities[Days until Completion] ) >= 0
&& MAX ( TasksOpportunities[Days until Completion] ) < 7, "Open",
MAX ( TasksOpportunities[Status] ) = "Open"
&& MAX ( TasksOpportunities[Days until Completion] ) > 7, "Standby",
MAX ( TasksOpportunities[Status] ) = "Completed", "0",
MAX ( TasksOpportunities[Status] ) = "Cancelled", "3",
BLANK ()
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.