CODEWITHSUNDEEP
ssasmdx
Matt Starn
Matt Starn

Reputation: 137

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:

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!

Upvotes: 1

Views: 1802

Answers (1)

Benoit
Benoit

Reputation: 1993

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))

Upvotes: 1

Related Questions