Dim Subset MDX for filter by leaf node but display parents?

[BigG]

Hi, this is an MDX for TM1 subset create question.

We have a dimension that has attributes for the 0 level items (not the rollups). We want to filter by the attribute but only display the ancestors. I have tried a few functions in MDX but the ones that would make sense (eg ancestor, parent) seem to be member specific (hard coded single member reference) so I cannot apply a 'filter' to to bring back the children leaf node level 0 and then display the ancestors of these results...or at least it does not seem you can.

Does anyone have a working example of this or suggestion?

THanks in advance

[Mike Cowie]

Hi:

This is MDX, so I'm sure there are many other ways to do this, some of which are probably simpler, but this was the first idea that popped up for me.

These samples first get the leaf elements filtered by attribute, and then use that to generate (via the GENERATE function) a (sub)set via various functions for each element ([Dimension].CurrentMember). GENERATE provides kind of a way of repeating an MDX sub-expression on a larger set of items generated from MDX, if you haven't used it before.

This will give you all ancestors of the filtered list including the leaf items:

Code: Select all

GENERATE( FILTER( TM1FILTERBYLEVEL( TM1SUBSETALL( [DimensionName] ), 0), [DimensionName].[AttributeName] = "AttributeFilterValue"), ASCENDANTS( [DimensionName].CurrentMember ) )

Similarly, you could use this if you didn't want the leaf elements:

Code: Select all

GENERATE( FILTER( TM1FILTERBYLEVEL( TM1SUBSETALL( [DimensionName] ), 0), [DimensionName].[AttributeName] = "AttributeFilterValue"), [DimensionName].CurrentMember.Ancestors )

If all you want are the first, immediate parents, you could do this:

Code: Select all

GENERATE( FILTER( TM1FILTERBYLEVEL( TM1SUBSETALL( [DimensionName] ), 0), [DimensionName].[AttributeName] = "AttributeFilterValue"), { [DimensionName].CurrentMember.Parent } )

And, finally, if you wanted ancestors at a set distance above you could use this:

Code: Select all

GENERATE( FILTER( TM1FILTERBYLEVEL( TM1SUBSETALL( [DimensionName] ), 0), [DimensionName].[AttributeName] = "AttributeFilterValue"), ANCESTORS( [DimensionName].CurrentMember, 1 ) )

Please note that TM1's support of MDX functions has generally improved over time, so if for some reason you're on a very old release (pre-9.0) it is possible some of the above examples may not work properly.

Hope that's helpful.

Regards,
Mike

[BigG]

THanks for the quick response,I think it was my syntax interpretation leading me astray. The examples you have given make sense. Cheers

[lotsaram]

Yes it is definitely possible to obtain the immediate parent using the TM1Rollup function. If you want to obtain all ancestors though you will need something else (maybe the DrillUpMember function? it is supposedly supported).

This will work to obtain the immediate parents regardless of level but exclude any N level members.

Code: Select all

{EXCEPT(
  {TM1ROLLUP(
    {FILTER( {TM1SUBSETALL( [Dim] )}, [Dim].[Attr] = AttrVal)},
    {FILTER( {TM1SUBSETALL( [Dim] )}, [Dim].[Attr] = AttrVal)}
  ) },
  {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Dim] )}, 0 )}
)}

Ahh I see Mr Cowie beat me to it but maybe it will be helpful anyway.

[afshin]

Thanks for the solution an absolute beauty, somebody must have read my mind. I was tearing my head over this just yesterday and decided to give it a go after the caffeine kicked in. Maybe I just need a beer now.

Cheers to all ..