I'm curious why [MyTestMember] this does not work correctly in this MDX statement.  It will calculate correctly, seemingly random, in SOME of the return cells but in many cells it will show NULL even though there is cube data in that month.

So for example it will return as follows.  All of these returned rows should have 547 in the last column:

MonthKey,CustKey,ProductKey,PurchaseAmount,MyTestMember

201401,1,10001,155,null

201402,1,10001,96,null

201403,1,10001,10,547

201404,1,10001,12,null

201405,1,10001,22,null

201406,1,10001,432,null

201407,1,10001,4,547

201408,1,10001,55,null

201409,1,10001,95,null

201410,1,10001,132,547

201411,1,10001,297,547

201412,1,10001,547,547

I believe it has something to do with the formula referencing a hierarchy that is also on an axis.

with 
member [MyTestMember] As ([Purchase Month].[Month Key].&[201412],[Purchase Amount]),NON_EMPTY_BEHAVIOR={[Fact Count]}
select non empty
{
[Measures].[Purchase Amount]
,[Measures].[MyTestMember]
} on columns,
non empty
{
nonempty(

nonempty( [Purchase Month].[Month Key].&[201401]:[Purchase Month].[Month Key].&[201412],([Fact Count]))	

* nonempty( [Customer].[Customer Key].[Customer Key],([Fact Count]))

* nonempty( [Products].[Product Key].[Product Key],([Fact Count]))

,([Fact Count]))
} on rows
from ProductCube

Hi Lee,

According to your description, you created a measure which give all members a static value. However, for some members it still show NULLs. Right?

In this scenario, the reason why you get the NULLs is setting the NON_EMPTY_BEHAVIOR property for your calculated measure. In Analysis Services, when you specify one or more measures in the Non-empty behavior list, Analysis Services treats the calculated member as empty if all the specified measures are empty. If the Non-empty behavior property is blank, Analysis Services must evaluate the calculated member itself to determine whether the member is empty. So if the [Fact Count] for the current members is empty, the whole calculated measure will return NULL. So just move the NON_EMPTY_BEHAVIOR part in your statement.  

For more information, please refer to articles below:

The Dangers of Non_Empty_Behavior

Budget Variance - A study of MDX optimizations: evaluation modes and NON_EMPTY_BEHAVIOR

Regards,

Thanks for the tip!  It wasn't the cause in this instance which was related to the grain of the underlying table.  It's a good point about the NON_EMPTY_BEHAVIOR though, we are conditioned to use that as much as possible but clearly there are situations where it should not be used.

• Marked as answer by Lee Cascio 2 hours 5 minutes ago