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

[juhoneyighot]

Hi! I'm new to PowerBI.

I'm using the IF statement, unfortunately I'm getting this error "Expressions that yield variant data-type cannot be used to define calculated columns."

Calculated Column: IF('stx__legacy_fsjob'[NC.IN]="","",(LEN('stx__legacy_fsjob'[_JobType_txt])-(LEN(SUBSTITUTE('stx__legacy_fsjob'[_JobType_txt],"_","")))))

Can anyone help me on this.

Thank you.

[audreygerred]

Hello! You might have a data type mismatch. In your DAX expression, you are combining different data types (e.g., text and integer) in the calculations. If Power BI cannot determine a consistent data type for the calculated column, it will result in a variant data type.

For example, if the "NC.IN" column in your data model contains both text and empty values, and you're trying to perform mathematical operations and string manipulations on the "JobType_txt" column, it might result in variant data types when there is a mix of data types involved.

[HotChilli]

All it means is that all data returned in the IF statement should be of the same type.  It looks like this one is returning text or numbers