Compare text and integers

[Elios]

Hello,

I am a beginner in Power BI.

I have the following problem, I have an excel code that works just fine in excel but when I translated it to DAX the following error message appears : "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."

Is there a way to force everything to be considered a text to bypass this ?

Where can I see the conflicting values ?

You'll find the code here :

Status = IF(Sheet8[Column1]="YES",

IF

(OR

(ISNUMBER

(SEARCH

("CS CD",Sheet8[Column2],1)),

OR(Sheet8[Column3]="CS BOULOGNE",OR(Sheet8[Colum4]="CS TOLOSA",Sheet8[Colum5]="PROJECTS"))),

IF

(AND

(Sheet8[Colum6]=0,

OR(Sheet8[Colum7]="",Sheet8[Colum8]="")

),"valid","conflict"),

IF

(AND

(Sheet8[Colum9]=1,

AND

(Sheet8[Colum10]>0,Sheet8[Colum11]>0)),

"conflict",

IF(

OR(

Sheet8[Colum12]=1,Sheet8[Colum13]=1)

,"valid",IF

(AND

(Sheet8[Colum14]="SECTOR",AND(Sheet8[Colum15]=Sheet8[Colum16],Sheet8[Colum17]=Sheet8[Colum18])),"Sector AG","")))),

"N/A")

It tries to verify data between columns and returns a line of text as a result depending on the IFs.

[v-robertq-msft]

Hi, @Elios 

Can Danextian’s solution that uses ISERROR() to find the location where the error occurred help you to solve your problem?

More detail of ISERROR() function.

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian · Accepted solution]

Hi @Elios ,

Have you tried wrapping your  formula with ISERROR? This should return TRUE/FALSE and then in the data view you can filter this to just TRUE and inspect the data. Otherwise you might have to clean your data first in Power Query - removing special characters and text from the field.

I agree with @PhilipTreacy , a sample data would be very useful. We understand its confidentiality so a dummy sample data would do - remove confidential information or obfuscate it one way or another.

[PhilipTreacy]

Hi @Elios 

You can't even copy/paste some examples of your data?

How can I know if you are trying to compare numbers stored as text or actual text strings? Or numbers and text mixed into cells?  How do I know which columns are text and which are number?

Your code refers to multiple columns. I can't recreate all of that.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523/jump...

If you are storing numbers as text, convert all of these to numbers.

Regards

Phil

[PhilipTreacy]

Hi @Elios 

Please supply sample data and explain what comparsons you are trying to make and it'll probably be easier to help you.

regards

Phil

[Elios]

Unfortunatly i cannot supply you with a sample data.

However the comparisons are colums containing text, some columns containing numbers. Since some of the checks contain >0 conditions, i cannot turn them into text.

Is there a way to see where the conflict happens from the Power BI interface ?