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'...
  • 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"
    )
)
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 😄