Search code examples
ssasmdx

Filtering an MDX Set Based on Another Set's Name

I'm still new to MDX so I'm not sure if there's an easy way, or a not-so-easy way to do what I'm asking, but here's a vague description of what I'm trying to do for my MDX query. I have two sets, both not directly related in the same hierarchy or cousins, although there may be a "family" call I don't know about that will do what I'm trying to do.

[Company].[Parent Company].[Parent Company] retrieves a set of all the members of companies that are parents of a company in the second set.

[Company].[Company].[Company] retrieves a set of all the members of companies themselves, including the parents themselves.

I'm trying to filter out the [C].[C].[C] set to NOT include any instances of a [Parent Company] in a MEMBER for my MDX query. I tried some weird stuff with Filter() but kept messing up sets/tuples or getting the wrong kind of data.
There isn't any grandparent/multi-generational trickery to these sets, there's only two levels to worry about (companies and their potential parents, there may not be any). If the cube was formatted better, it would had been nice if they were in a hierarchy, but that's not the case here.

Example output:

  • Members of [Company].[Parent Company].[ParentCompany] = {A, C, E}
  • Members of [Company].[Company].[Company] = {A, B, C, D, E, F, G}

Then I want to get a set that will return the following: {B, D, F, G} and that's it.

Any help would be appreciated!

Solution

  • You can do this without using the Member_Name:

    Exclude([Company].[Company].[Company].Members, Extract([Company].[Company].[Company].Members * [Company].[Parent Company].[Parent Company].Members, [Company].[Company]))

    or

    Exclude([Company].[Company].[Company].Members, Exists([Company].[Company].[Company].Members, [Company].[Parent Company].[Parent Company].Members))