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
unclejemima
Post Patron
Post Patron

Error Compare Number with Text...but its all numbers already?

I have this formula...

Skid_Avail_Less_Than_5_At_S1 = if (Inventry[Skid_Avail_S1]<5 & Inventry[Skid_Avail_FLSS]>0,[Skid_Avail_S1]-5,"")
 
I'm getting this error...
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.
 
Thing is, both my values
(Inventry[Skid_Avail_S1] & Inventry[Skid_Avail_FLSS] are both "Decimal Number" Data types...so I don't get the problem with the error?

Can someone reformat my formula to encompass VALUE or FORMAT to fix the issue?

Thanks!
1 ACCEPTED SOLUTION
unclejemima
Post Patron
Post Patron

Got the bugger to work!  Fixed my IF AND statement to read this...

 

Skid_Short_S1 = 
IF (
 Inventry[Skid_Avail_S1] <= 5
 && Inventry[Skid_Avail_FLSS]>0 
,
 Inventry[Skid_Avail_S1]-5,blank()
)

View solution in original post

7 REPLIES 7
unclejemima
Post Patron
Post Patron

Got the bugger to work!  Fixed my IF AND statement to read this...

 

Skid_Short_S1 = 
IF (
 Inventry[Skid_Avail_S1] <= 5
 && Inventry[Skid_Avail_FLSS]>0 
,
 Inventry[Skid_Avail_S1]-5,blank()
)
unclejemima
Post Patron
Post Patron

I tried the switch but i must have something off still as I'm getting the error "SWITCH' does not support comparing values of tru/false with values of type number"

 

Here what I tried to get a multi IF...

 

Skid_Avail_Less_Than_5_At_S1_Test = 
SWITCH(Inventry[Skid_Avail_S1]<5 && Inventry[Skid_Avail_FLSS]>1,Inventry[Skid_Avail_S1]-5, blank())
unclejemima
Post Patron
Post Patron

Sorry, that was a calc...(Skid_Avail_S1).

I changed the formula to be like this now...

Skid_Avail_Less_Than_5_At_S1 =
IF (
    Inventry[Skid_Avail_S1] < 5 & Inventry[Skid_Avail_FLSS] > 0,
    Inventry[Skid_Avail_S1] - 5,
    BLANK ()
)

But did some more testing and it appears its the & in my statement that it does not like.  I'm trying to do an IF AND statement and the format must be wrong.


Can you think of a SWITCH formula I can use to say IF Inventry[Skid_Avail_S1] < 5 AND Inventry[Skid_Avail_FLSS] > 0,


I think that will solve the problem!

jdbuchanan71
Super User
Super User

In your screen shot you show the data type for Skid_Avail_FLSS in both pictures.  What is Skid_Avail_S1?

jdbuchanan71_0-1621537304120.png

Can you share your .pbix file (load it do OneDrive and share the link)?

 

unclejemima
Post Patron
Post Patron

I looked to make sure, and all fields being referenced in the calculation ARE infact number formatted.  The error is bizarre?

 

unclejemima_0-1621536454172.png

 

jdbuchanan71
Super User
Super User

@unclejemima 

Try it like this, I think it might be the ""  on the end.

 

Skid_Avail_Less_Than_5_At_S1 =
IF (
    Inventry[Skid_Avail_S1] < 5 & Inventry[Skid_Avail_FLSS] > 0,
    [Skid_Avail_S1] - 5,
    BLANK ()
)

 

Hey jdbuchanan71.  Very cool!  Thank you...but it didn't work.  Still getting the same error...

 

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.

 

Using the error suggestions, can you think of a VALUE or FORMAT function to insert to convert the values so it would work?

 

Thanks and look forward to your reply,

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.