MDX: How to exclude ancestors from being returned in this query?

Question

I have this MDX query:

Exists([Group].[Group Hierarchy].allmembers,
    {[Group].[Group Full Name].&[121 - Group A], [Group].[Group Full Name].&[700000 - Group C]})

... which works fine EXCEPT that it returns all of the ancestors of the specified groups as well. What I want is to return JUST the groups from the hierarchy with the specified Group Names (this is a type 2 dimension so there may be many at different levels).

Any ideas?

Answer 1

filter([Group].[Group Hierarchy].members, instr(@GroupGroupFullName,[Group].[Group Hierarchy].Properties( "Group Full Name" )))

Answer 2

I suspect that the issue is with the reference to [Group].[Group Hierarchy].allmembers. I'm guessing that [Group Hierarchy] is some sort of navigational hierarchy with multiple levels. It sounds like what you need to do is to replace [Group Hierarchy] with the name of the attribute that contains your type 2 group members in order to get a list of all the members that exist with the specified "Group Full Name" members.

I would leave Filter(..., Instr()) approach as a last resort as it would be much slower than the equivalent set based operation.