Forum Discussion

rosamhernandez1's avatar
rosamhernandez1
Helper I
8 months ago
Solved

Nested IF

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)

 

Need Help
  • amitchandak's avatar
    amitchandak
    8 months ago

    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"

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    8 months ago

    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"

  • Zanqueta's avatar
    Zanqueta
    Super User
    8 months ago

    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 🌀.

     

  • Nabha-Ahmed's avatar
    Nabha-Ahmed
    Super User
    8 months ago

    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"