Error while retrieving datetime column from LOOKUPVALUE expression inside SWITCH function

[alisson_vilela]

Hi all,

I am creating a calculated column, and given it requires more than 2 conditions, using the SWITCH / TRUE structure. This new column is in Table1, which has no relation with Table2 and Table3.

The code goes like this:

SWITCH(
TRUE(),
Table1[Class] in VALUES(Table2[Class]), LOOKUPVALUE(Table3[Date], Table3[Index],Table1[Index]),
<value2>, “Result2”,
“Else”

)

However, I keep getting the "Expressions that yield variant data-type cannot be used to define calculated columns" error message. I've tried the code, testing it part by part, and found that:

1. The message only comes up when the data retrieved by the LOOKUPVALUE function is a datetime. If the Result_ColumnName is any other column in the searched table, the code runs with no messages.
2. This same LOOKUPVALUE expression, when used apart from the SWITCH TRUE structure, works fine.
3. Table3[Index] and Table1[Index] used in the function are index columns with unique values and a 1:1 correlation.

Does anyone have any idea of what may be causing the error message? Thanks very much in advance.

[amitchandak · Accepted solution]

@alisson_vilela , this error come when return two different datatype in switch/if else

like text and number

in such case convert number to text by using [Value] & "" or format to text. or return blank in place of text