CODEWITHSUNDEEP
excelpowerbidax
Rafael Osipov
Rafael Osipov

Reputation: 740

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

I get this error on 2 fields typed as Decimal Number with this formula in Power Pivot calculated column. Аnyone know how can I debug?

IF(HASONEVALUE (Tifuli[Seniority] ), VALUES ( Vetek_Tbl[Amount]), "gift" )

Upvotes: 4

Views: 41787

Answers (3)

tasmia
tasmia

Reputation: 1

I was facing the problem today modifiedDQCycle = IF(DQ_RULES[DQ_CYCLE] = [MaxDQCycleRules], "Latest",DQ_RULES[DQ_CYCLE]) for this expression..and this modifiedDQCycle = IF(DQ_SUMMARY_RESULTS[DQ_CYCLE] = [MaxDQCycle], "Latest",DQ_SUMMARY_RESULTS[DQ_CYCLE])

Although both of them are the same expression , one was working one was not. Just because the later had value from a number column and the first expression had all text value. So, if u use number data type, use for all. if you want to use text modify the column value to text.

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40204

Andrey is correct. You cannot have mixed type columns of number and text.

It doesn't make sense to try to convert the text "gift" to a number, but a possible workaround is to convert your number to text so that your column is a text column instead of mixed type.

The FORMAT function is available to convert a value to text.

Upvotes: 2

Andrey Nikolov
Andrey Nikolov

Reputation: 13440

Calculated columns are computed once, when you load the data, and stored in your model. They are strongly typed, e.g. number. Your expression returns either Amount (number) or "gift" (text). This means that some of the rows in your data must be numbers, while others must be texts. But all values in one column must have the same data type. So your expression is invalid for this use case. You must re-evaluate your design.

Upvotes: 8

Related Questions