MDX - Filtering members against multiple string values in a reference cube

[JohnO]

I have a cube with 2 dimensions, very similar to an attribute cube. First dimension is our Asset dimension, the second is a set of measures, one of which is the Profit Centre that the asset is associated with. This is updated by the users via a picklist. And yes this could then be represented as a true hierarchy but we are not there.

What I want to be able to do is filter the leaf level members by a restricted list of Profit Centres.

This works fine (Just started with the generated code from the set editor, I know there are different ways to establish the leaves)

FILTER(DESCENDANTS([Capital Planning Asset].[Capital Planning Asset].[Total Capital Planning Assets
] , 99 , LEAVES) , [CTL Capital Planning Asset].([CTL Capital Planning Asset Measure].[Profit Centre]) = "IT")

But what want is to substitute "IT" with a dynamic MDX list based on the Profit Centre dimension.

DISTINCT(DESCENDANTS([Profit Centre].[Profit Centre].[Stores Incl MGTRPT] , 99 , LEAVES))

Possible?

[gtonkin]

Hi John,

You could do this using the Generate() function by using the 3rd parameter to concatenate the members into a string.
Once we have a string we can then use INSTR() to test if the member's attribute/lookup value matches to something in the string and keep if it does.

My example is using Product where brand is another rollup and essentially I am keeping all Products where its Brand attribute value is in the generated string.

Code: Select all

FILTER(
	{TM1FILTERBYLEVEL(TM1SUBSETALL([Product].[Product]) , 0)},
	INSTR(1, 
		Generate(
			{TM1FILTERBYLEVEL(DRILLDOWNMEMBER({[Product].[Product].[Total Brands]} , {[Product].[Product].[Total Brands]} , RECURSIVE) , 1)},
			[Product].[Product].CurrentMember.Name,
			"; "),
			[Product].[Product].CurrentMember.Properties("Brand"),
			1) >0
		)
			

Edit: Another option may be something like this:

Code: Select all

GENERATE(
	[Brand].[Brand].[_S-Reporting Brands].CHILDREN , 
	FILTER(TM1FILTERBYLEVEL(TM1SUBSETALL([Product].[Product]) , 0) , 
	[Brand].[Brand].CURRENTMEMBER.NAME = [Product].[Product].CURRENTMEMBER.PROPERTIES("Brand")))

This essentially starts with your Profit Centre list then for each Asset item, you could retrieve the Profit Centre and compare.

[JohnO]

Hi John,

You could do this using the Generate() function by using the 3rd parameter to concatenate the members into a string.
Once we have a string we can then use INSTR() to test if the member's attribute/lookup value matches to something in the string and keep if it does.

Thanks George

That was a great help although I admit it took me a while to understand what you had done. I settled on the following (Using the original MDX I posted above). It is interesting that there is nothing similar to the IN clause in SQL as a single value to match = is hardly realistic. I also looked at SetToStr as an alternative to GENERATE but it puts in {} which confuses things and then I looked for a REPLACE to get rid of those....

FILTER(
{DESCENDANTS([Capital Planning Asset].[Capital Planning Asset].[Total Capital Planning Assets] , 99 , LEAVES) },
INSTR(1,
Generate(
{DISTINCT(DESCENDANTS([Profit Centre].[Profit Centre].[Stores Incl MGTRPT] , 99 , LEAVES)) },
[Profit Centre].[Profit Centre].CurrentMember.Name,
"; "),
[CTL Capital Planning Asset].([CTL Capital Planning Asset Measure].[Profit Centre]),
1) >0
)

I think this is a useful one for reference purposes.

[gtonkin]

Awesome! Glad it worked out for you.

So much to learn on MDX still but it is really powerful stuff.

[Wim Gielis]

That was a great help although I admit it took me a while to understand what you had done. I settled on the following (Using the original MDX I posted above). It is interesting that there is nothing similar to the IN clause in SQL as a single value to match = is hardly realistic. I also looked at SetToStr as an alternative to GENERATE but it puts in {} which confuses things and then I looked for a REPLACE to get rid of those....

FILTER(
{DESCENDANTS([Capital Planning Asset].[Capital Planning Asset].[Total Capital Planning Assets] , 99 , LEAVES) },
INSTR(1,
Generate(
{DISTINCT(DESCENDANTS([Profit Centre].[Profit Centre].[Stores Incl MGTRPT] , 99 , LEAVES)) },
[Profit Centre].[Profit Centre].CurrentMember.Name,
"; "),
[CTL Capital Planning Asset].([CTL Capital Planning Asset Measure].[Profit Centre]),
1) >0
)

I think this is a useful one for reference purposes.

Thanks George and John.

A remark from my side:
in case the cube cell for the Profit centre is empty, it's still included in the list (as per my limited testing). Might not be so uncommon as a case in reality. Then an additional Filter on non-empty cells will need to be added in. Or profit centers like IT and another profit center containing "it" in the name could be a flagged incorrectly. Therefore, let's add in ";" in front of and after the search string (the cube value):

Code: Select all

FILTER(
{DESCENDANTS([Capital Planning Asset].[Capital Planning Asset].[Total Capital Planning Assets] , 99 , LEAVES) },
INSTR(1,
";" + Generate(
{DISTINCT(DESCENDANTS([Profit Centre].[Profit Centre].[Stores Incl MGTRPT] , 99 , LEAVES)) },
[Profit Centre].[Profit Centre].CurrentMember.Name,
";") + ";",
";" + [CTL Capital Planning Asset].([CTL Capital Planning Asset Measure].[Profit Centre]) + ";",
1 ) > 0 )

5 times ";" in total but probably the safest solution if you use option 1. Option 2 of George is good too.

[JohnO]

Therefore, let's add in ";" in front of and after the search string (the cube value):

Even better.

Thanks Wim