Values comparision number to text

[tejapowerbi123]

Hi,

Can any one help for below error?

In excel I have a valid price column and in that column has $amount and “Contract not started” comments, but when  I try to replicate below formula in power bi  I am getting below error.

 Start Date = IF([Valid Price]="CONTRACT NOT STARTED","N","Y")

Data Type for valid price in power bi =Decimal

DAX comparison operations do not support comparing values of type Number with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

thanks

[AlB]

Hi @tejapowerbi123

It is not allowed to compare text and numbers. 

Maybe use a number code for "CONTRACT NOT STARTED", like a negative number that I guess wont come up in prices? 

[tejapowerbi123]

I tried below,

Start Date = IF([Valid Price]=" ","N","Y")

but getting the same error.

[AlB]

@tejapowerbi123

Sure, you're still doing the same, comparing a number to text. 

You could use the query editor to easily replace all occurrences of your text string with a number

[tejapowerbi123]

Hi AIB,

problem is that VALID PRICE is a calculated column and i am not able to see in QUERY EDITOR.

[AlB]

@tejapowerbi123

Hang on, I'm getting confused here. If it's a calculated column,  how have you been able to create one with numbers and text? What type is that column?

[tejapowerbi123]

Hi,

Please see below forumula 

Valid Price1 = IF((' Contracts'[Start Year]+[ Start Month]/12)<=([AUP Current Year]+[AUP Current Month]/12),[Monthly Discounted Price]&"" ,"Contract Not Yet Started")

***All columns data type is whole number,

[AlexisOlson · Accepted solution]

The data type of column [Valid Price] cannot be whole number or decimal if you have any rows with "Contract Not Yet Started". It would have to be text. I guess you don't have any rows that have that value?

[tejapowerbi123]

thank you so much AIB and Alexis i will share PBIX as soon as i can.Thanks

[tejapowerbi123]

Hello Alexis & AIB,

Thank you so much for your suggestion.I have changed the "Contract Not Yeat Started" to 00000 and that works for me.

[AlB]

@tejapowerbi123

is this not text?? The concatenation [Monthly Discounted Price] & "" should produce text

Maybe it'd be quicker if you shared your pbix