cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User
Super User

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.