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

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's avatar
    enterprised1
    Icon for Helper I rankHelper I
    10 years ago

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