topic Re: 0 not recognized as a number in DAX Commands and Tips

0 not recognized as a number

Anonymous — Fri, 17 Jan 2020 14:43:30 GMT

I'm trying to do a command but I can't work out how to get it to recognise 0 as a number rather than a blank, the issue I have is I have both blanks and 0's in my column, this is my command:

Day Groups = IF('Claims Full + Complaints'[Days from Claim to Complaint]=BLANK(), "No Complaint",IF('Claims Full + Complaints'[Days from Claim to Complaint]=0,"Within 15 days", IF('Claims Full + Complaints'[Days from Claim to Complaint]<16,"Within 15 days", IF('Claims Full + Complaints'[Days from Claim to Complaint]<31,"Within 30 days", IF('Claims Full + Complaints'[Days from Claim to Complaint]<61,"Within 60 days", IF('Claims Full + Complaints'[Days from Claim to Complaint]>60,"Over 60 days"))))))

The result is both blanks and 0's come out as "No Complaints".

Hopefully someone can help? Thanks

Re: 0 not recognized as a number

HotChilli — Fri, 17 Jan 2020 15:51:54 GMT

Use ISBLANK(column) rather than '= BLANK'

Re: 0 not recognized as a number

Anonymous — Fri, 17 Jan 2020 15:56:34 GMT

Sorry how do I work that into my command I already have? Thank you

Re: 0 not recognized as a number

HotChilli — Fri, 17 Jan 2020 16:13:24 GMT

In the first clause , remove the '= BLANK()' and put isblank before the column name (with brackets round the column)

Re: 0 not recognized as a number

Anonymous — Fri, 17 Jan 2020 16:19:23 GMT

Perfect thank you for your help 😊

Re: 0 not recognized as a number

Peter_R — Fri, 17 Jan 2020 23:01:06 GMT

.. by the way, although the code works, nested IFs like that are a nasty way to accomplish it. SWITCH is a much more readable way to go - like this:
Day Groups = SWITCH(
TRUE(),
ISBLANK( 'Claims Full + Complaints'[Days from Claim to Complaint] ), "No Complaint",
'Claims Full + Complaints'[Days from Claim to Complaint] = 0, "Within 15 days",
'Claims Full + Complaints'[Days from Claim to Complaint] < 16, "Within 15 days",
'Claims Full + Complaints'[Days from Claim to Complaint] < 31, "Within 30 days",
'Claims Full + Complaints'[Days from Claim to Complaint] < 61, "Within 60 days",
"Over 60 days"
)