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

~ Vitalie Ciobanu

Error:

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

Cause:

IF based Calculated Columns in Power BI or Power Pivot with 2 different data types is not allowed.

Solution:

Format one data type as needed using FORMAT() function.

[code language=”sql”]

Age = IF (
TableName[Birthday] < TODAY(),
FORMAT (
Year ( TODAY() )- Year ( TableName[Birthday] ),"General Number" ),
"Invalid birth date"
)

[/code]

FORMAT Function (DAX): https://msdn.microsoft.com/en-us/query-bi/dax/format-function-dax

Pre-Defined Numeric Formats for the FORMAT function: https://msdn.microsoft.com/query-bi/dax/pre-defined-numeric-formats-for-the-format-function