Evaluate multiple fields for blanks

[enterprised1]

Hi,

I have 3 fields which I want to evaluate to see if a process is complete.  My idea is to create a new column that says "Not Started," "Incomplete," or "All Complete."  If there's a better way to do it, i'm open to suggestions as well. 

Here's what I'm trying to do: If there's a date/timestamp in all 3 fields, it should say "All Complete" in the new column.  If any (but not all) of the fields have a datestamp, it should say "Incomplete."  If there are no datestamps in any of the fields, it should say "Not Started"

When I use the following nested IF statement,

IF(AND(ISBLANK([#"Date1"]),ISBLANK([#"Date2"]),ISBLANK([#"Date3"])),"Not Started",IF(OR(ISBLANK([#"Date1"]),ISBLANK([#"Date2"]),ISBLANK([#"Date3"])),"Incomplete",IF(AND(NOT(ISBLANK([#"Date1"])),NOT(ISBLANK([#"Date2"])),NOT(ISBLANK([#"Date3"]))),"All Complete")))

the Query Editor returns "Expression error: The name 'IF' wasn't recognized. Make sure it's spelled correctly."

Any ideas on what I'm doing wrong?

Thanks!

[SabineOussi · Accepted solution]

@Sean is right! I only focused on the formula and did not pay attention to query editor.

My solution would work in data view using DAX by adding a new calculated column, from there you do the proper calling to dates instead of using #.

So in your case by adding a custom column from the query editor, you should write the following

if
([#"Date1"]="" and [#"Date2"]="" and [#"Date3"]="")
then "Not Started"
else if
([#"Date1"]="" or [#"Date2"]="" or [#"Date3"]="")
then "Incomplete"
else "All Complete"

I'm also learning as I'm trying to help!

[enterprised1]

That worked like a charm!  Thanks a million!  Been trying to figure that one out for days!

[Sean]

this error indicates you are trying to write in M but using DAX syntax

M is very case sensitive - if.... then.... else (if)

[SabineOussi]

The problem is ANDs and ORs only take two logical arguments and you're giving them three.

The below statement should work fine:

IF(AND(AND(ISBLANK([#"Date1"]),ISBLANK([#"Date2"])),ISBLANK([#"Date3"])),"Not Started",

IF(OR(OR(ISBLANK([#"Date1"]),ISBLANK([#"Date2"])),ISBLANK([#"Date3"])),"Incomplete",

IF(AND(AND(NOT(ISBLANK([#"Date1"])),NOT(ISBLANK([#"Date2"]))),NOT(ISBLANK([#"Date3"]))),"All Complete")))

Hope this helps!