Forum Discussion

MeganLouise93's avatar
MeganLouise93
Regular Visitor
1 year ago
Solved

Need help with Dax IF Calculation

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

 

 

  • johnt75's avatar
    johnt75
    1 year ago

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

5 Replies

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

    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's avatar
      MeganLouise93
      Regular Visitor
      1 year ago

      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's avatar
        johnt75
        Icon for Super User rankSuper User
        1 year ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      MeganLouise93
      Regular Visitor
      1 year ago

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