MDX - How to use subset from one hierarchy to filter and generate subset on another hierarchy

[lav4you]

Hi MDX experts,

I am trying to generate a subset based on two hierarchy from same dimension.

I am trying this in PAW. I have dimension Name 'Product'.
This dimension has two hierarchies.

'Product' and 'Leaves'

On 'Product' hierarchy I have subset name 'Engineering'.
Now I would like to apply this subset on 'Leaves' hierarchy and generate a dynamic subset through MDX in which it all leaf level members from 'Engineering' hierarchy.

Is this possible. I have tried using different approaches like TM1SubsetTo function or Generate function but everytime I get the CellsetMismatch: error.

For Eg.

Code: Select all

TM1SubsetToSet([Product].[Product],"Engineering","Public")

Or

Code: Select all

 {Intersect(
 [Product].[Product].[Engineering],
 [Product].[Leaves].Members
 )
 }

Give following error

provide a title
Validation failed.
Could not execute MDX expression. Error Code:rte295. Error:INTERSECT: Sets have incompatible dimensionality

Any help How can I achieve the same?

Regards,

Lav

[declanr]

Something like the below should work; it sounds like you were on the right track.
The trick is to think of hierarchies as completely separate entities, so you can take the "Name/Description" of an element from one (but that could come from anywhere as its just a bit of text) and the Member Unique Name that you build up in StrToMember is something completely different.

Code: Select all

Generate
	(
		{TM1FilterByLevel({TM1SubsetToSet( [Product].[Product], 'Engineering' )},0)},
		{StrToMember ( "[Product].[Leaves].[" + [Product].[Product].CurrentMember.Name + "]" )}
	)

[lav4you]

Thanks Declan,

This is great. I will tweak it a bit further to match some of my other filtering requirements.

But really helps.

Cheers,

Lav