cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills 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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors