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.
It looks like most comments confirm that the result of the second query is a bug. Some more comments here in this other thread