Page 1 of 1
Subset Filter Elements By List of Attributes From Another Dimension
Posted: Thu Nov 03, 2022 4:08 am
by 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?
Re: Subset Filter Elements By List of Attributes From Another Dimension
Posted: Thu Nov 03, 2022 7:27 am
by 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()
Re: Subset Filter Elements By List of Attributes From Another Dimension
Posted: Thu Nov 03, 2022 8:28 am
by 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
Re: Subset Filter Elements By List of Attributes From Another Dimension
Posted: Thu Nov 03, 2022 9:29 am
by lotsaram
Should be possible. But you would need to use the Generate function.
Re: Subset Filter Elements By List of Attributes From Another Dimension
Posted: Thu Nov 03, 2022 10:50 am
by 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") + "]")}
)
Re: Subset Filter Elements By List of Attributes From Another Dimension
Posted: Thu Nov 03, 2022 12:14 pm
by 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.
Re: Subset Filter Elements By List of Attributes From Another Dimension
Posted: Thu Nov 03, 2022 12:18 pm
by 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!
Re: Subset Filter Elements By List of Attributes From Another Dimension
Posted: Thu Nov 03, 2022 12:29 pm
by 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.
Re: Subset Filter Elements By List of Attributes From Another Dimension
Posted: Thu Nov 03, 2022 1:57 pm
by lotsaram
raeldor wrote: ↑Thu Nov 03, 2022 12:18 pm
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.