MDX currentmember/IS inconsistent?

Question

I have three queries to filter by a member using the currentmember function. When the filter is applied to the hierarchy that has the member I want to filter by, I can match the members using the IS operator and get the correct result. It does not work though when the filtered set and the member are in different hierarchies. Yet, I can get the filtered results correctly for the second case if instead of objects comparison I just do a caption comparison. The examples use the AdventureWorks database.

This query is working as expected with the IS operator:

select non empty [Measures].[Reseller Sales Amount] on 0,
Filter (NonEmpty({[Geography].[Country].[Country].ALLMEMBERS * [Geography].[City].[City].ALLMEMBERS}), [Geography].[City].Currentmember IS [Geography].[City].&[Seattle]&[WA]) on 1

from [Adventure Works]

This one uses a caption comparison (different result, as expected)

select non empty [Measures].[Reseller Sales Amount] on 0,
Filter (NonEmpty({[Geography].[Country].[Country].ALLMEMBERS}), [Geography].[City].Currentmember.MEMBER_CAPTION = 'Seattle') on 1

from [Adventure Works]

This one though, which should produce the same result as the previous query, does not return anything:

select non empty [Measures].[Reseller Sales Amount] on 0,
Filter (NonEmpty({[Geography].[Country].[Country].ALLMEMBERS }), [Geography].[City].Currentmember IS [Geography].[City].&[Seattle]&[WA]) on 1

from [Adventure Works]

Thanks.

I have no idea why this question was voted down without any comment or answer. — RalfS

FrankPl FrankPl · Accepted Answer · 2013-08-27T12:18:21

In fact, this is a bit strange. For me, the most surprising result is the second one. No reference in the set to be filtered to the city, and nevertheless, a filter is applied. I would think the reason for the second result is that somehow "implicit overwrite" kicks in.

And probably, the second and third case are treated differently as the optimizer somehow choses different ways to interpret the statement. Normally, string operations like the reference to caption are less efficient than the IS operator which works on object identity.

MDX currentmember/IS inconsistent?

2 Answers