Forum Discussion

enterprised1's avatar
enterprised1
Icon for Helper I rankHelper I
10 years ago
Solved

Evaluate multiple fields for blanks

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...
  • SabineOussi's avatar
    SabineOussi
    10 years ago

    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!

     

     

SabineOussi's avatar
SabineOussi
Icon for Skilled Sharer rankSkilled Sharer
10 years ago

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!