DAX IF Statement not working

[tbucki1]

I have a data model built in Power Pivot with a few tables connected and I am trying to do a simple formula based on a few conditions.

Basically calculate this formula if Scenario 1, 2, and 3 are true. Below is a view of the Error:

The way it is written is like a nested IF Statement, but I am getting an Error in Excel saying:

"MdxScript(Model) (8, 4) Calculation Error in Measure 'Revenue_Model'[Actual Net New Calc]: The value for column 'Percent Owned' in table 'HFM Map' cannot be determined in the current context. etc.....

Here is the formula:
Actual Net New Calc:=IF([Actuals]=BLANK(),BLANK(),
IF('HFM Map'[Operations]="Direct Ops - Core Retail",BLANK(),
IF('HFM Map'[Percent Owned]="Not Owned",BLANK(),
[Actuals]-[Budget]+[Q2 Forecast Net New])))

So if Actuals are blank, do nothing
If Operation is equal to "Direct Ops - Core Retail, do nothing
If Percent Owned is = "Not Owned", do nothing.
SO if it doesn't match any of these then perform the formula.

Am I not doing something right with the IF Statement? Seems to also break with just one IF Statement as well.

I.e. This doesn't work either
Actual Net New Calc:=IF('HFM Map'[Operations]="Direct Ops - Core Retail",BLANK(),
[Actuals]-[Budget]+[Q2 Forecast Net New])

Error Received:
"MdxScript(Model) (8, 4) Calculation Error in Measure 'Revenue_Model'[Actual Net New Calc]: The value for column 'Operation' in table 'HFM Map' cannot be determined in the current context. etc.....

 

[akice]

Measures (aka formulas as you put it) in Dax are aggregation tools. They work with summarized data. Your are wanting to compare values on a row by row basis, but your measure is not operating under a row context to allow that. That is why you are getting the error you quoted. Dax doesn't know which row you are referring to. There are many different ways to do what you want, but too much info to get into for one post. However, this may be close to what you want:

Actual Net New Calc := CALCULATE ( [Actuals] - [Budget] + [Q2 Forecast Net New], 'HFM Map'[Operations]<>"Direct Ops - Core Retail", 'HFM Map'[Percent Owned]<>"Not Owned"  )

then for final measure to go on pivot:

Final measure  := IF ( NOT ( ISBLANK( [Actual] ) ), [Actual Net New Calc] )

although I can't tell you if it is performant or not with info given.

You have to learn how to think in Dax which is different than thinking in Excel. Good luck.