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
philippa_f
Frequent Visitor

DAX for SWITCH statement on calculated column - how to include VALUE function?

Hi

 

I have a calculated column in my table that works out number of Days to Expiry, and I then need another calculated column to allocate descriptors based on that number to give me 'Expiry Status'. I am trying the following:

 

Expiry Status = SWITCH(
TRUE (),
'MyTable'[DaystoExpiry] = "", "No Exp. date",
'MyTable'[DaystoExpiry] < 0, "Expired",
'MyTable'[DaystoExpiry] > 366, "Exp. date > 1 yr",
"Exp. date <= 1 yr"
)
 
 
But this gives me an error message that: 
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. 
 
I have checked DaystoExpiry and the data type is Decimal number. Some are blanks, which is expected. How can I fix this with VALUE or FORMAT in my DAX?
1 ACCEPTED SOLUTION
philippa_f
Frequent Visitor

Thanks everyone for trying to help. I have just got it to work using the following:

Expiry Status =
SWITCH (TRUE(),
'MyTable'[DaystoExpiry] =BLANK(), "No Exp. date",
'MyTable'[DaystoExpiry] < 0, "Expired",
'MyTable'[DaystoExpiry] < 366, "Less than 1yr",
'MyTable'[DaystoExpiry] > 365, "More than 1yr"
)
 
I think the issue was the order of my logic, together with using "" when I should have been using BLANK().
 
Got there in the end with your combined help 🙂

View solution in original post

10 REPLIES 10
philippa_f
Frequent Visitor

Thanks everyone for trying to help. I have just got it to work using the following:

Expiry Status =
SWITCH (TRUE(),
'MyTable'[DaystoExpiry] =BLANK(), "No Exp. date",
'MyTable'[DaystoExpiry] < 0, "Expired",
'MyTable'[DaystoExpiry] < 366, "Less than 1yr",
'MyTable'[DaystoExpiry] > 365, "More than 1yr"
)
 
I think the issue was the order of my logic, together with using "" when I should have been using BLANK().
 
Got there in the end with your combined help 🙂
AUDISU
Resolver II
Resolver II

@philippa_f 
Hi,

Try following code.

 

Expiry Status =
VAR NoofDays = SUM(MyTable[DaystoExpiry])
RETURN
SWITCH(TRUE() ,
NoofDays = 0, "No Exp. date",
NoofDays < 0, "Expired",
NoofDays > 366, "Exp. date > 1 yr",
"Exp. date <= 1 yr"
)

AUDISU_0-1657858899144.png

Thanks

Hi AUDISU

 

Thanks. I tried this, but it gives a result of 'Expired' for every line of data, although there are definitely some that should be in each category. Any idea what I am doing wrong? Data type is text, if I change it to numbers I just get an error for everything, with a message saying Cannot convert value 'Expired' of type Text to type Integer.

 

I really appreciate you taking the time to help with this.

 

Philippa

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Sorry! I can't do this as it's private organisational stuff 😞
I will try to create a dummy anonymised version though.

Hi Philippa,

 

Can I see your DAX formula?

 

Thanks

Sure:
 
Expiry Status =
VAR NoofDays = SUM('MyTable'[DaystoExpiry])
RETURN
SWITCH(TRUE() ,
NoofDays = 0, "No Exp. date",
NoofDays < 0, "Expired",
NoofDays > 366, "Exp. date > 1 yr",
"Exp. date <= 1 yr"
)

Hi Philippa,

 

Please check data type of 'MyTable'[DaystoExpiry] column.

If it is not in number format change it to number.

 

Thanks

 

FarhanAhmed
Community Champion
Community Champion

Kindly check the datatype of "'MyTable'[DaystoExpiry]"

 

When you are calculating null/blank values for MyTable'[DaystoExpiry] it is better to return BLANK not "" which causes to enforce column to Text datatype.

 







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

Proud to be a Super User!




Hi FarhanAhmed

Thanks so much for getting back to me. I did substitute BLANK in the code above instead of "" - was this what you meant?

Like this: 

 

Expiry Status = SWITCH(
TRUE (),
'MyTable'[DaystoExpiry] = BLANK, "No Exp. date",
'MyTable'[DaystoExpiry] < 0, "Expired",
'MyTable'[DaystoExpiry] > 366, "Exp. date > 1 yr",
"Exp. date <= 1 yr"

 

But it is still not working, giving me an incorrect syntax error.  Can you spot what I have done wrong? Many thanks in advance!

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.