If function with a text column and a mixed column with both text and number

[Anonymous]

Hi Everyone,

I have two columns, column A = "Yes" and "No" values, all text
Column B = numbers (1,5,10,16,20 & "n/a" & "cohort error") thus column B contains both numbers and text and PowerBi classes it as a text column.

My issue comes when I want to run an If formula, so I am trying to do * IF(column A) = "No" && (Column B) < 5, "yes", "no". I get the error "Dax comparison operations do not support comparing values of type text with values of type integer.consider using the Value or Format function to convert one of the values.

I have tried using both the Value and Format function to no avail. If I use the Value function on column B - it tells me that my text values e.g. "n/a" cannot be converted to number etc.

I am not sure what to do as the text values in column B "n/a" etc have to be there as they are pulling from a lookup.

Thank you in advance

Regards,

P

[Thim · Accepted solution]

You could use this Dax function to make a new column.

IFERROR(VALUE(Table2[Column B]);1000000)

This will change all text values and errors to 1.000.000. (Feel free to chose another number, but it does have the be a number)

Then use the new column as replacement for the column B in your formula.

If you don't want a new column, you can type it all in one Formula.

IF(Table2[Column A]="No" && IFERROR(VALUE(Table2[Column B]);1000000)<5;"Yes";"No")

Hope this will help. 🙂

[Anonymous]

Thank you for your help 🤗🤗