Error: expressions that yield variant data types cannot be used to define calculated columns

[luckygirl]

Hi,

I have the following DAX to calculate subsequent date.

SusequentCompletedDate =
VAR CurrentKey = Table[ClientCode]
VAR CurrentDate = Table[CompletedDate]
VAR NextCompletedDate = CALCULATE (  MIN ( Table[CompletedDate] ) , ALL ( Table), Table[ClientCode] = CurrentKey, Table[CompletedDate] > CurrentDate )
VAR NextG = MINX ( FILTER ( ALL ( Table), AND ( Table[ClientCode] = CurrentKey, Table[CompletedDate] = NextCompletedDate ) ), Table[CompletedDate] )
RETURN IF( NextG <> BLANK(), NextG, "No Subsequent Product" )

It gives me error "expressions that yield variant data types cannot be used to define calculated columns". Can someone let me know why that happens?

[luckygirl]

Thank you. Return NextG, worked for me. 

[OwenAuger · Accepted solution]

Hi @luckygirl 

A DAX expression that is capable of returning different data types under different conditions is deemed to have a "variant" data type.

The Tabular engine does not allow that any column of a table loaded to the model to have a variant data type.

The expression for SusequentCompletedDate can return either of:

1. The variable NextG (which appears to be of type date)
2. "No Subsequent Product" (type string)

I would recommend returning blank rather than a text value when NextG is blank, so you could change the last line to:

RETURN NextG

Note that measures are permitted to return variant data types.

Regards

[luckygirl]

Thank you. Yes, understood and Return NextG, worked for me :). 

[ArwaAldoud]

Hi @luckygirl 

The IF statement is returning two different data types NextG, which is a date value and
"No Subsequent Product", which is a text (string) value.
try to use the BLANK() or , you can use a default date eg DATE(1900,1,1)

IF( NextG <> BLANK(), NextG, BLANK() )
or
IF( NextG <> BLANK(), NextG, DATE(1900,1,1) )

If this response was helpful, please accept it as a solution and give kudos to support other community members.

[luckygirl]

Thank you. Return NextG, worked for me. 

[Deku]

In a calculated column to can only have a single data type returned. You are returning a date or string. You could use format( date, "dd-mmm-yy") to cast the date into a string 

If this was a measure, variants outputs are possible