Nested IF

[rosamhernandez1]

Need help adjusting formula to only be calculated when the Development Status is "Active-Dev" or "Active-Exe"

Original formula is: 

if [Interconnection Facilities ISD] <> null and

Duration.Days([Projected In Service Date] - [Interconnection Facilities ISD]) >= 0 and

Duration.Days([Projected In Service Date] - [Interconnection Facilities ISD]) <= 30 then

"Yes"

else

"No"

Proposed formula is:

If [Development Status] <> "Active – Dev" or "Active – Exe" then null

else

(

[Interconnection Facilities ISD] <> null and

Duration.Days([Projected In Service Date] - [Interconnection Facilities ISD]) >= 0 and

Duration.Days([Projected In Service Date] - [Interconnection Facilities ISD]) <= 30 then

"Yes"

else

"No"

)

receiving error in the 2nd "then" (italicized above)

[Nabha-Ahmed]

Hi @Zanqueta 

You need to check the Development Status first, and then run your original test block inside a nested if…then…else.

✅ Correct M Code

if [Development Status] = "Active-Dev"
or [Development Status] = "Active-Exe" then
if [Interconnection Facilities ISD] <> null and
Duration.Days([Projected In Service Date] - [Interconnection Facilities ISD]) >= 0 and
Duration.Days([Projected In Service Date] - [Interconnection Facilities ISD]) <= 30 then
"Yes"
else
"No"
else
null

---

✔ Why your formula failed

Power Query M doesn’t allow else (condition then … else …)

The correct structure is always:

if … then
if … then … else …
else
…

Also A <> "X" or "Y" is invalid.
Must be written as:

A = "X" or A = "Y"

[Zanqueta]

Hi @rosamhernandez1,

The error happens because Power Query (M) does not allow a then statement inside parentheses without a complete if ... then ... else structure. Your proposed logic needs to be rewritten so that the condition for Development Status wraps the entire expression.

I suggest your adapt using this code:

if [Development Status] = "Active-Dev" or [Development Status] = "Active-Exe" then
    if [Interconnection Facilities ISD] <> null and
       Duration.Days([Projected In Service Date] - [Interconnection Facilities ISD]) >= 0 and
       Duration.Days([Projected In Service Date] - [Interconnection Facilities ISD]) <= 30 then
        "Yes"
    else
        "No"
else
    null

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

[amitchandak · Accepted solution]

@rosamhernandez1 , Power query code 

= if [Development Status] <> "Active – Dev" and [Development Status] <> "Active – Exe" then
null
else
if [Interconnection Facilities ISD] <> null
and Duration.Days([Projected In Service Date] - [Interconnection Facilities ISD]) >= 0
and Duration.Days([Projected In Service Date] - [Interconnection Facilities ISD]) <= 30
then "Yes"
else "No"