MDX calculated member dimension context

Question

I have the following calculated member which represents the quantity of "overstocked" products:

WITH 
    MEMBER [Measures].[Overstocked Items Count] AS 
        FILTER(
            [Items].[Item No].CHILDREN,
            [Measures].[Overstocked Qty] > 0
        ).COUNT

It works just fine for any linked to the measure group dimension except for the Items dimension itself and the reasons are obvious. Is there a way to create a calculated member that would respect the context it is evaluated in? So basically if this member is evaluated against an item group code I need items count by those groups, not the entire items set.

Is it possible to post the whole script including the select and where clauses? — whytheq
There is no script as such - it is intended to be a cube calculated member. The EXISTING keyword does the trick though. Thank a lot! — v8vb
please be aware that EXISTING will not always work. I will add some more detail to my answer. — whytheq

whytheq whytheq · Accepted Answer · 2016-08-28T11:27:39

EXISTING is a useful keyword that can add the current context to your measure:

WITH 
    MEMBER [Measures].[Overstocked Items Count] AS 
        FILTER(
            EXISTING([Items].[Item No].CHILDREN),
            [Measures].[Overstocked Qty] > 0
        ).COUNT

EXISTING is very good when you want to know the members present from a different hierarchy within the same dimension. e.g. say you have U.S.A selected from the country hierarchy (in geography dimension) and you need to count state/county members from a stateCounty hierarchy that is also part of the geography dimension then EXISTING is the correct choice.

If you want to go across dimensions so say you have U.S.A selected and you'd like to count customer, from the customer dimension who are associated with the U.S.A then I don't think EXISTING will work - you'll need to explore either EXISTS or NONEMPTY.

MDX calculated member dimension context

1 Answers