# Showing Measure values as either ‘0’ or ‘NULL’ in cube ?

Hello all,

It been long time  ,after a long time gap I would love to post this blog which might be helpful to the one who works in SSAS.

While designing a cube everyone simply doesn’t care about the ‘NULL’ or  ‘0’ values within the cube output. So through this blog , I want to share with you guys that how can we show the NULL  or 0 value output within the cube.

Also , if anyone need to show the exact client data either it’s NULL or as 0 within a Cube then for them this blog might be helpful too.

One can handle/Preserve the NULL values of data by following the blog by DevinKnight which is one of nice blog I referred

http://bidn.com/blogs/DevinKnight/ssis/1587/ssas-preserve-measure-null-values

Since , BIDS will treat all the NULL values as ‘0’ while calculation and if you have hundreds of calculated measure and you want to show the values as either NULL or 0 as per you need.

`And there you need to handle the 4cases mostly NULL/NULL,NULL/0 , 0/0, 0/NULL.`

If divisor is zero, then there will be no error, the cell value simply will be positive or negative infinity represented as 1.#INF – but people don’t like this for some reason and prefer to replace it with NULL, which makes the user wonder why there is no data for that calculated Measure.

```Let’s say, we have a calculated measure as [Measure].[Cal] and you need to show like this.

[Measure].[Cal] =[Measure].[A]/ [Measure].[B]

case 1 when [Measure].[A]=0 and [Measure].[B]=0, then show 0,

case 2 when [Measure].[A]= NULL and [Measure].[B]=NULL then show NULL,

Case 3 when [Measure].[A]= 0 and  [Measure].[B]=NULL then show 0,viceversa

Case4 when  [Measure].[A]= value1 and  [Measure].[B]=value2 then show value1/value2,```

The best approach to calculating [Measures].[Cal] is the following:

` IIF([Measures].[B] = 0, null, [Measures].[A] / [Measures].[B] )`

If you return null, NON EMPTY will work and the calculation will be faster as there are optimizations in the engine to handle this specific pattern.

Least  one used:

` IIF([Measures].[B] = 0, 0, [Measures].[A] / [Measures].[B] )`

You don’t want to return a 0 in the second argument of the IIF as this will always return a value and will mean that you cannot use NON EMPTY in your queries (as the calculation would never return an empty value).

The above all 4 case can be handle within one nested IIF condition as you only want to display NULL if both A and B are NULL, otherwise it is always 0 or the number.:

```IIF(ISEMPTY(A) AND ISEMPTY(B), NULL, IIF(A=0 OR B = 0, 0, A/B)) i.e

IIF(

ISEMPTY([MEASURES].[A]) AND ISEMPTY([MEASURES].[B]), NULL,

IIF([MEASURES].[A] = 0 OR [MEASURES].[B]=0 , 0, [MEASURES].[A]/[MEASURES].[B])

)
Some more details to handle like this are further describe within Mosha Blog.
http://sqlblog.com/blogs/mosha/archive/2005/06/30/how-to-check-if-cell-is-empty-in-mdx.aspx```

Hope this post will be helpful for all of us in the field of BI mainly in SSAS/MDX and one who is looking for such solution.

Regards,

Anil Maharjan