How to remove AS Infinity from calculation result (-1.#INF)

I'm getting an infinity (i think) result from one of my calculations and would like to know if it can be replaced with something more visually friendly (such as nothing!)

Fact Table has the following relevant fields

Contribution

AverageBalance

And a calculated measure called NoDays, which is the Distinct Count of the Fact Table FK_DateKey

Now my calculation for Yield is as follows

(([Measures].[Contribution]/[Measures].[NoDays])*365)/ABS([Measures].[Average Value Balance])

Now this works in the majority of cases except I sometimes get the old -1.#INF value returned on drilling down to dimension levels, for example when (i'm guessing) one of the above results in a divide by zero issue or some such combination of figures that result in AS math confusion!

Is there some sort of "catch all" function that will help me to return something more friendly when my calculation returns the above error ?

The second part of this Blog post by Mosha can help you if you are running SSAS2005.

http://sqljunkies.com/WebLog/mosha/archive/2007/01/28/iif_performance.aspx

Regards

Thomas Ivarsson

|||

Cheers Thomas,

Using the following in the calculation seems to have solved it with no apparent perfomance issues (as yet)

IIF([Measures].[Average Value Balance]=0,NULL,(([Measures].[Contribution]/

[Measures].[NoDays])*365)/

ABS([Measures].[Average Value Balance]))

My Thanks for the link to the article