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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.