Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Elios
Regular Visitor

Compare text and integers

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.

 

1 ACCEPTED SOLUTION
danextian
Super User
Super User

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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

5 REPLIES 5
v-robertq-msft
Community Support
Community Support

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
Super User
Super User

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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
PhilipTreacy
Super User
Super User

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

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


PhilipTreacy
Super User
Super User

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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 ?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors