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

[Mk60]

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

[Bibiano_Geraldo · Accepted solution]

All negative numbers start with ' is this expected?

Eg: '-0999

[Anonymous · Accepted solution]

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]

Thanks much, Jarvis! This solution worked well. 

[Bibiano_Geraldo]

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]

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]

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

[Mk60]

Yeah, unfortunately that column is the text and that's my problem. I tried to change the type in the table to the whole number but it does not let me do that. Do you know any other way to change the column data type? Thanks for your time and willingness, much apprecited!

What kind of data sample would be helpful, I could try to recreate something to give you some better clues? 

[Bibiano_Geraldo]

Hi @Mk60 , as a mentioned, your table contains really text, thats why will not allow you to change data type, thats why i asked a sample file, can be with only this column, to see closer the problem. maybe are there spaces or anything that is numeric.

Make sure to not share a sensitive information:

[Mk60]

Unfortunately I don't have good option to send you just this column in PBI file. I tried to export few columns in excel, but can't attach it here. This is a sample of that colum, not sure if this could be of any reference at all? 

[Bibiano_Geraldo]

Hi @Mk60,

You can upload to onedrive and share the link for download here in comments. 

[Mk60]

Thank you for that suggestion. I requested my data provider to see if he can change the field format and I could update my table. If that does not happen, I'll try your suggestion and create onedrive account, as I do not have one right now. Trully apprecite your willingnes to help Bibiano_Geraldo!

[Bibiano_Geraldo]

Can be in any cloud service, onedrive was my suggestion, but you can check if the values are number in excel using isnumber function, this will return true if yes, if not will return false 

[Bibiano_Geraldo · Accepted solution]

All negative numbers start with ' is this expected?

Eg: '-0999

[Mk60]

Just wanted to let you know that your suggestions helped me to navigate to clean my data. I apprecite your willingness to help me to resolve this data nightmare. Thaks again!

[SacheeTh]

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]

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

[SacheeTh]

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