DAX IF Statement not working

tbucki1

New Member
Joined
Dec 26, 2014
Messages
12
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.....
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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:
Code:
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:

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top