none
wrong calculation of measure when the value is zero RRS feed

  • Question

  • Hi,

    I have a calculated measure which calculates based on DimSite.
    The calculation is as below -
    CREATE MEMBER CURRENTCUBE.[MEASURES].[Measure1]
     AS
        case [Site].[Site].Currentmember
            when [Site].[Site].[Site Description].&[Site1] then ([Measures].[Rate] * 120)       
            when [Site].[Site].[Site Description].&[Site2] then ([Measures].[Rate] * 60)
            when [Site].[Site].[Site Description].&[Site3] then ([Measures].[Rate] * 60)
            when [Site].[Site].[Site Description].&[Site4] then ([Measures].[Rate] * 60)
            when [Site].[Site].[Site Description].&[Site5] then ([Measures].[Rate] * 60)
        end,
    VISIBLE = 1;
    I have created a hierarchy and I am using that in the calculation.

    This code works fine but in some case it doesn't.
    When the value of Measure Rate is zero (0) for Site1 then it multiplies all the other sites with 120 where as it should multiply it with 60 as per the calculation.
    If the value of Site1 Rate is > 0 then this measure works fine.

    Did anyone come across this type of strange problem ? Is there any way to fix this ?


    Anirban Biswas (v-anbis@microsoft.com)
    Thursday, October 1, 2009 6:52 AM

Answers

  • Referring to an earlier thread from the forum (below), using the simple type of Case can cause problems. Does using the searched type of case instead solve the issue?

    Possible MDX bug - CASE statement with NULL and Aggregate?
    - Deepak
    • Marked as answer by Anirban Biswas Thursday, October 1, 2009 8:26 AM
    Thursday, October 1, 2009 7:07 AM
    Moderator
  • Thanks a lot Deepak for ur help. I am able to solve the issue. Deepal is right, I had to change it as below -

        case
            when [Site].[Site].CURRENTMEMBER IS [Site].[Site].[Site Description].&[Site1] then ([Measures].[Rate] * 120)
            when [Site].[Site].CURRENTMEMBER IS [Site].[Site].[Site Description].&[Site2] then ([Measures].[Rate] * 60)
            when [Site].[Site].CURRENTMEMBER IS [Site].[Site].[Site Description].&[Site3] then ([Measures].[Rate] * 60)
            when [Site].[Site].CURRENTMEMBER IS [Site].[Site].[Site Description].&[Site4] then ([Measures].[Rate] * 60)
            when [Site].[Site].CURRENTMEMBER IS [Site].[Site].[Site Description].&[Site5] then ([Measures].[Rate] * 60)
        end


    Anirban Biswas (v-anbis@microsoft.com)
    • Marked as answer by Anirban Biswas Thursday, October 1, 2009 8:25 AM
    Thursday, October 1, 2009 8:25 AM

All replies

  • Referring to an earlier thread from the forum (below), using the simple type of Case can cause problems. Does using the searched type of case instead solve the issue?

    Possible MDX bug - CASE statement with NULL and Aggregate?
    - Deepak
    • Marked as answer by Anirban Biswas Thursday, October 1, 2009 8:26 AM
    Thursday, October 1, 2009 7:07 AM
    Moderator
  • Thanks a lot Deepak for ur help. I am able to solve the issue. Deepal is right, I had to change it as below -

        case
            when [Site].[Site].CURRENTMEMBER IS [Site].[Site].[Site Description].&[Site1] then ([Measures].[Rate] * 120)
            when [Site].[Site].CURRENTMEMBER IS [Site].[Site].[Site Description].&[Site2] then ([Measures].[Rate] * 60)
            when [Site].[Site].CURRENTMEMBER IS [Site].[Site].[Site Description].&[Site3] then ([Measures].[Rate] * 60)
            when [Site].[Site].CURRENTMEMBER IS [Site].[Site].[Site Description].&[Site4] then ([Measures].[Rate] * 60)
            when [Site].[Site].CURRENTMEMBER IS [Site].[Site].[Site Description].&[Site5] then ([Measures].[Rate] * 60)
        end


    Anirban Biswas (v-anbis@microsoft.com)
    • Marked as answer by Anirban Biswas Thursday, October 1, 2009 8:25 AM
    Thursday, October 1, 2009 8:25 AM