Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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!
@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!
That worked like a charm! Thanks a million! Been trying to figure that one out for days!
this error indicates you are trying to write in M but using DAX syntax
M is very case sensitive - if.... then.... else (if)
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |