topic Re: Need help with Dax IF Calculation in DAX Commands and Tips

Need help with Dax IF Calculation

MeganLouise93 — Tue, 10 Dec 2024 12:50:46 GMT

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

)

Re: Need help with Dax IF Calculation

johnt75 — Tue, 10 Dec 2024 13:29:49 GMT

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

Re: Need help with Dax IF Calculation

MeganLouise93 — Tue, 10 Dec 2024 14:38:21 GMT

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." 😔

Re: Need help with Dax IF Calculation

johnt75 — Tue, 10 Dec 2024 15:07:28 GMT

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.

Re: Need help with Dax IF Calculation

Anonymous — Wed, 11 Dec 2024 07:01:40 GMT

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.

Re: Need help with Dax IF Calculation

MeganLouise93 — Wed, 11 Dec 2024 10:15:03 GMT

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