Need help with Dax IF Calculation

[MeganLouise93]

Hi there,

I'm pretty new to Dax, and am trying to get an If statement to work - I have 3 outcomes, and the data in the original field is numbers and blanks.

This is how far I've gotten but I'm seeing errors with Text vs integer data types.. Can anyone tell me what I need to do to get this to work?

Open Vs Effective Date Buckets = IF('All ServiceNow Data'[Open Vs Effective Date]="",

"Invalid Effective Date",

IF('All ServiceNow Data'[Open Vs Effective Date]<0,

"Opened Before Effective Date",

"Opened on/after Effective Date")

)

[Anonymous]

Thank you for your prompt relpy! johnt75 

Hi @MeganLouise93 

I'm trying to reproduce your error, when the 'Open Vs Effective Date' column type is Text, the error will occur.

You can use the following two methods to solve this problem.

First: Change the type from text to number

Second: Use Value function

Open Vs Effective Date Buckets = 
IF (
    ISBLANK ( VALUE('All ServiceNow Data'[Open Vs Effective Date] )),
    "Invalid Effective Date",
    IF (
        VALUE('All ServiceNow Data'[Open Vs Effective Date] ) < 0,
        "Opened Before Effective Date",
        "Opened on/after Effective Date"
    )
)

Best Regards,

Jayleny

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

[MeganLouise93]

Thank you for your response - Johnt75's solution worked perfectly as I had already amended the fields to numbers 😄

[johnt75 · Accepted solution]

Try

Open Vs Effective Date Buckets =
IF (
    ISBLANK ( 'All ServiceNow Data'[Open Vs Effective Date] ),
    "Invalid Effective Date",
    IF (
        'All ServiceNow Data'[Open Vs Effective Date] < 0,
        "Opened Before Effective Date",
        "Opened on/after Effective Date"
    )
)

[MeganLouise93]

Thank you - Unfortunately this is still returning the error "DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values." 😔

[johnt75]

Check the type of 'All ServiceNow Data'[Open Vs Effective Date] and make sure it is one of the numeric types, it sounds like it is text at the moment.