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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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 III
Resolver III

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.