Subset Filter Elements By List of Attributes From Another Dimension

[raeldor]

I want to filter elements in a dimension (Vintage) to show only those that are used in ANY element on another dimension (Versions) in the attribute 'Vintage'. Is this possible using an MDX subset?

[gtonkin]

May be easier to if we can see an example of the two dimensions and their attributes.

Alternatively, have a look in the MDX guide in Tips and Tricks section for some ideas.
You will likely need to start with Generate()

[raeldor]

Thank you so much for your reply. Here is a screenshot. I'm basically trying to get a unique list of elements of the 'MAP Vintages' dimension that are used in the 'Vintage' column of the 'MMAP Versions' dimension. And ONLY those that are used.
Thank you

[lotsaram]

Should be possible. But you would need to use the Generate function.

[gtonkin]

Generate needs two components - the first being a set to enumerate and the second being where you are doing something for each item in the first set.

Essentially step 1 is to pick up MMAP with a Vintage.
Step two is to convert those Vintages to members using StrToMember from the attribute string.

Try something like this:

Code: Select all

GENERATE(
FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [MMAP] )}, 0)},[MMAP].CurrentMember.Properties("Vintage")<>""),
{STRTOMEMBER("[MMAP].[" + [MMAP].CurrentMember.Properties("Vintage") + "]")}
)

[raeldor]

Thank you for the reply. The trouble is the attribute is on a different dimension. I tried to modify your code as...

Code: Select all

GENERATE(FILTER(TM1FILTERBYLEVEL(TM1SUBSETALL([MMAP Vintages]) , 0) , 
[MMAP Versions].CURRENTMEMBER.PROPERTIES("Vintage") <> "") , 
{STRTOMEMBER("[MMAP Vintages].[" + [MMAP Versions].CURRENTMEMBER.PROPERTIES("Vintage") + "]")})

But of course, I can't use [MMAP Versions].CURRENTMEMEBER because there is no current member as it's on another dimension. I need it to somehow iterate through all those members in the second dimension in order to filter the first dimension.

[raeldor]

Ah, I got it! I should be looping through the second dimension like such...

Code: Select all

GENERATE(FILTER(TM1FILTERBYLEVEL(TM1SUBSETALL([MMAP Versions]) , 0) , 
[MMAP Versions].CURRENTMEMBER.PROPERTIES("Vintage") <> "") , 
{STRTOMEMBER("[MMAP Vintages].[" + [MMAP Versions].CURRENTMEMBER.PROPERTIES("Vintage") + "]")})

This works great, Thank you!

[gtonkin]

Yes, I was a bit sneaky there and called it all MMAP throughout.

The point was for you (and others reading this) to understand the concept of the Generate() then apply it, which you did.

[lotsaram]

Ah, I got it! I should be looping through the second dimension like such...

Code: Select all

GENERATE(FILTER(TM1FILTERBYLEVEL(TM1SUBSETALL([MMAP Versions]) , 0) , 
[MMAP Versions].CURRENTMEMBER.PROPERTIES("Vintage") <> "") , 
{STRTOMEMBER("[MMAP Vintages].[" + [MMAP Versions].CURRENTMEMBER.PROPERTIES("Vintage") + "]")})

This works great, Thank you!

Excellent. Well done! The concept of looping one set to generate another set is a tricky one, the hierarchies don't need to be the same, as long as you have some property to join or match on then it can work very nicely for such use cases like you have.