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'. 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?
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 🙂

10 Replies

  • FarhanAhmed's avatar
    FarhanAhmed
    Community Champion
    4 years ago

    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.

     

    • philippa_f's avatar
      philippa_f
      Frequent Visitor
      4 years ago

      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!

  • AUDISU's avatar
    AUDISU
    Resolver III
    4 years ago

    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"
    )

    Thanks

    • 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

      • AUDISU's avatar
        AUDISU
        Resolver III
        4 years ago

        Hi Philippa,

         

        Can I see your DAX formula?

         

        Thanks

  • philippa_f's avatar
    philippa_f
    Frequent Visitor
    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 🙂