Forum Discussion

philippa_f's avatar
philippa_f
Frequent Visitor
4 years ago
Solved

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...
Need Help
  • philippa_f's avatar
    philippa_f
    4 years ago

    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 🙂
philippa_f's avatar
philippa_f
Frequent Visitor
4 years ago

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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

Hi,

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

  • philippa_f's avatar
    philippa_f
    Frequent Visitor
    4 years ago

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