locked
IsEmpty to avoid divide by zero RRS feed

  • Question

  • Hi experts,

    I'm new to SSAS / MDX and trying to add a Month over Month % Growth calculation to my cube. As expected, i receive a divide by zero error when I encounter a month that has no previous month.

    To avoid this i am using IsEmpty as follows:

    CREATE MEMBER CURRENTCUBE.[Measures].[MOM Cost Growth %]
     AS 
    CASE WHEN IsEmpty(ParallelPeriod([Consignment Date].[Month].[Month], 1, [Consignment Date].[Month].CurrentMember))
    THEN NULL
    ELSE
    (([Consignment Date].[Month].CurrentMember, [Measures].[Consignment Cost]) - 
    (ParallelPeriod([Consignment Date].[Month].[Month], 1, [Consignment Date].[Month].CurrentMember), 
    [Measures].[Consignment Cost])) / (ParallelPeriod([Consignment Date].[Month].[Month], 1,[Consignment Date].[Month].CurrentMember), 
    [Measures].[Consignment Cost])
    END
    , 
    FORMAT_STRING = "Percent", 
    VISIBLE = 1 ;             
    

    When i do this, the cube turns no results for this measure i.e. it always evaluates to empty. I suspect it has something to do with using CurrentMember here....how do i get around this.

    Thanks in advance for any assistance.

    Clay.

     

     

     

    Friday, May 13, 2011 6:17 AM

Answers

  • This is because you have not included a reference to the [Consignment Cost] measure in your IsEmpty test, this will cause the expression to use the current measure which in the case of a calculate measure will be the [MOM Cost Growth %] measure itself which will make the expression recursive and cause it to walk back to the start of time!

    It's better to just test for 0, empty cells are automatically coalesced as 0 so this will handle both nulls and empty cells

    eg.

    IIF( (ParallelPeriod([Consignment Date].[Month].[Month], 1,[Consignment Date].[Month].CurrentMember),
    [Measures].[Consignment Cost]) = 0

    ,NULL

    ,(([Consignment Date].[Month].CurrentMember, [Measures].[Consignment Cost]) -
    (ParallelPeriod([Consignment Date].[Month].[Month], 1, [Consignment Date].[Month].CurrentMember),
    [Measures].[Consignment Cost])) / (ParallelPeriod([Consignment Date].[Month].[Month], 1,[Consignment Date].[Month].CurrentMember),
    [Measures].[Consignment Cost])
    ) ,
    FORMAT_STRING = "Percent",
    VISIBLE = 1 ; 

     


    http://darren.gosbell.com - please mark correct answers
    Friday, May 13, 2011 7:07 AM