Forum Discussion

rosamhernandez1's avatar
rosamhernandez1
Icon for Helper I rankHelper I
9 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...
Need Help
  • amitchandak's avatar
    amitchandak
    9 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
Icon for Super User rankSuper User
9 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 🌀.