Post reply

Restrict a calculated member to a dimension hierarchy

  • April 13, 2011 at 1:03 pm

    #248862

    Hi,

    Does anyone know if I can restrict a calculated measure to a hierarchy of a dimension. In my case have a Channel dimension that contains Direct and Indirect both with additional children. I have a loaded measure " Returns" that I want to only associated with sub Channel Direct and it's children and not Indirect. I have created a calculated measure "Direct Retunrs" however the Direct Return amount still populates under the Indirect channel when browsing the cube. Any thought would be greatly appreciated!

    Thanks,

    Steve

  • April 22, 2011 at 8:01 am

    #1314879

    Hi Steven,

    If I understand your structure correctly, you have a hierarchy with two members, "Direct" and "Indirect". If this is the case, you can fix the dimensionality of the calculated measure. You can do something like this:

    CREATE MEMBER CURRENTCUBE.[Measures].[Direct Returns]

    SUM([Measures].[Returns],[Channel].[i][b]LevelName[/b][/i].[Direct]);

    You will notice however that when you attempt to slice any calc which is 'fixed' dimensionally (depending on your settings) you probably receive the ALL CELL value for that calc. However, any other intersection should behave as intended.

    Chris

    Chris Umbaugh
    Data Warehouse / Business Intelligence Consultant
    twitter @ToledoSQL

  • April 22, 2011 at 10:27 am

    #1314939

    Thanks for your reply Chris! I will check it out.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply