Forum Discussion

Mk60's avatar
Mk60
Icon for Resolver I rankResolver I
1 year ago
Solved

How to fix dax operation error using Values or Format in the string

Would you please show me where/how I can use VALUE or FORMAT in this dax calculation to avoid this error. 

My column "F_PROP_DSCR" is text and I tried to change te format to whole number but it does not let me. Please advise. Thank you!

DSC Bucket = IF(AND('First Addenda'[F_PROP_DSCR] >= 1.00,'First Addenda'[F_PROP_DSCR] <= 1.25),"1.25-1.00",

IF(AND('First Addenda'[F_PROP_DSCR] >= 1.26,'First Addenda'[F_PROP_DSCR] <= 1.50),"1.50-1.26",

IF(AND('First Addenda'[F_PROP_DSCR] > 1.50,'First Addenda'[F_PROP_DSCR] <= 1.75),"1.75-1.51",

IF(AND('First Addenda'[F_PROP_DSCR] > 1.75,'First Addenda'[F_PROP_DSCR] <= 2.00),"2.00-1.76",

IF(AND('First Addenda'[F_PROP_DSCR] > 0.00,'First Addenda'[F_PROP_DSCR] < 1.00),"Below 1:1",

IF(('First Addenda'[F_PROP_DSCR] > 2.00),"Greater than 2.00",

"UNKNOWN"))))))

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Mk60 

     

    From your screenshots, you can see that the data type of the "F_PROP_DSCR" column is "Text", and the reason for the error in the DAX formula is that you can't compare the text with the value.
    I think what you need to do is to clean up your data in Power Query Editor and change the "F_PROP_DSCR" column to numeric type. Then this DAX formula will work successfully.

     

    1. Change the “F_PROP_DSCR” column to a numeric type in the Power Query Editor.

    2. Remove error rows or replace error rows.

    If there are fewer rows of text, you can also choose to manually replace the text value with the correct numeric value.

     

    Best Regards,
    Jarvis Tang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

16 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Mk60 

     

    From your screenshots, you can see that the data type of the "F_PROP_DSCR" column is "Text", and the reason for the error in the DAX formula is that you can't compare the text with the value.
    I think what you need to do is to clean up your data in Power Query Editor and change the "F_PROP_DSCR" column to numeric type. Then this DAX formula will work successfully.

     

    1. Change the “F_PROP_DSCR” column to a numeric type in the Power Query Editor.

    2. Remove error rows or replace error rows.

    If there are fewer rows of text, you can also choose to manually replace the text value with the correct numeric value.

     

    Best Regards,
    Jarvis Tang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

    • Mk60's avatar
      Mk60
      Icon for Resolver I rankResolver I
      1 year ago

      Thanks much, Jarvis! This solution worked well. 

  • SacheeTh's avatar
    SacheeTh
    Icon for Resolver II rankResolver II
    1 year ago

    Hi Mk60 ,

    Here is the Corrected Mesure

    DSC Bucket =
IF(
    AND(VALUE('First Addenda'[F_PROP_DSCR]) >= 1.00, VALUE('First Addenda'[F_PROP_DSCR]) <= 1.25), "1.25-1.00",
    IF(
        AND(VALUE('First Addenda'[F_PROP_DSCR]) >= 1.26, VALUE('First Addenda'[F_PROP_DSCR]) <= 1.50), "1.50-1.26",
        IF(
            AND(VALUE('First Addenda'[F_PROP_DSCR]) > 1.50, VALUE('First Addenda'[F_PROP_DSCR]) <= 1.75), "1.75-1.51",
            IF(
                AND(VALUE('First Addenda'[F_PROP_DSCR]) > 1.75, VALUE('First Addenda'[F_PROP_DSCR]) <= 2.00), "2.00-1.76",
                IF(
                    AND(VALUE('First Addenda'[F_PROP_DSCR]) > 0.00, VALUE('First Addenda'[F_PROP_DSCR]) < 1.00), "Below 1:1",
                    IF(
                        VALUE('First Addenda'[F_PROP_DSCR]) > 2.00, "Greater than 2.00",
                        "UNKNOWN"
                    )
                )
            )
        )
    )
)

    Explanation:
    (i) VALUE Function: This function converts the text value in F_PROP_DSCR into a numeric value that can be used in comparisons. It is applied to every occurrence of 'First Addenda'[F_PROP_DSCR] in the formula.
    (ii) Conditions: Now, the comparisons like >= and <= work properly, as the column is interpreted as numeric during the evaluation.

    Optional: Formatting Numbers
    If you need to display numbers in a specific format (like 1.00), you can use the FORMAT function in other measures or calculated columns where applicable. However, for logical comparisons, VALUE is the appropriate function.

    By making these changes, the DAX calculation will no longer throw errors, and it will correctly evaluate the bucket logic.

     

    • Mk60's avatar
      Mk60
      Icon for Resolver I rankResolver I
      1 year ago

      Thanks you so much for the quick reply, SascheeTh! I used your string but got this new error now. Any suggestion to this?

       

      • SacheeTh's avatar
        SacheeTh
        Icon for Resolver II rankResolver II
        1 year ago

         

        The error occurs because some values in the column 'First Addenda'[F_PROP_DSCR] are text, and the VALUE function is attempting to convert them to a number, which is not possible.

        To fix this issue, you can add a condition to check whether the value can be converted to a number before using it. Here's the updated DAX formula:

         

        DSC Bucket =
IF(
    ISNUMBER(VALUE('First Addenda'[F_PROP_DSCR]),
    IF(
        AND(VALUE('First Addenda'[F_PROP_DSCR]) >= 1.00, VALUE('First Addenda'[F_PROP_DSCR]) <= 1.25), "1.25-1.00",
        IF(
            AND(VALUE('First Addenda'[F_PROP_DSCR]) >= 1.26, VALUE('First Addenda'[F_PROP_DSCR]) <= 1.50), "1.50-1.26",
            IF(
                AND(VALUE('First Addenda'[F_PROP_DSCR]) > 1.50, VALUE('First Addenda'[F_PROP_DSCR]) <= 1.75), "1.75-1.51",
                IF(
                    AND(VALUE('First Addenda'[F_PROP_DSCR]) > 1.75, VALUE('First Addenda'[F_PROP_DSCR]) <= 2.00), "2.00-1.76",
                    IF(
                        AND(VALUE('First Addenda'[F_PROP_DSCR]) > 0.00, VALUE('First Addenda'[F_PROP_DSCR]) < 1.00), "Below 1:1",
                        IF(
                            VALUE('First Addenda'[F_PROP_DSCR]) > 2.00, "Greater than 2.00",
                            "UNKNOWN"
                        )
                    )
                )
            )
        )
    ),
    "Invalid Value"
)

         

         

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Icon for Super User rankSuper User
    1 year ago

    Hi Mk60 ,
    i simplified your DAX and made more readable by using the SWITCH function. Here's an optimized version of your formula:

    DSC Bucket = 
SWITCH(
    TRUE(),
    VALUE('First Addenda'[F_PROP_DSCR]) >= 1.00 && VALUE('First Addenda'[F_PROP_DSCR]) <= 1.25, "1.25-1.00",
    VALUE('First Addenda'[F_PROP_DSCR]) >= 1.26 && VALUE('First Addenda'[F_PROP_DSCR]) <= 1.50, "1.50-1.26",
    VALUE('First Addenda'[F_PROP_DSCR]) > 1.50 && VALUE('First Addenda'[F_PROP_DSCR]) <= 1.75, "1.75-1.51",
    VALUE('First Addenda'[F_PROP_DSCR]) > 1.75 && VALUE('First Addenda'[F_PROP_DSCR]) <= 2.00, "2.00-1.76",
    VALUE('First Addenda'[F_PROP_DSCR]) > 0.00 && VALUE('First Addenda'[F_PROP_DSCR]) < 1.00, "Below 1:1",
    VALUE('First Addenda'[F_PROP_DSCR]) > 2.00, "Greater than 2.00",
    "UNKNOWN"
)

     

    • Mk60's avatar
      Mk60
      Icon for Resolver I rankResolver I
      1 year ago

      Thank you very much, Bibiano_Geraldo. I apprecite your SWITCH suggestion, looks much cleaner for sure. However, I still get this error when trying to use this calculated column? Any thoughts on this one?

      This is what the column format looks like:

       

      • Bibiano_Geraldo's avatar
        Bibiano_Geraldo
        Icon for Super User rankSuper User
        1 year ago

        Hi Mk60 , its possible to share no sensitive sample data? its look that the selected column have text, that's why its not accepting to convert to number