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

Post Reply
JohnO
Posts: 106
Joined: Mon Jul 29, 2019 5:02 am
OLAP Product: Planning Analytics
Version: 2.0.9.x
Excel Version: Office 365 16

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

Post by 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?
User avatar
gtonkin
MVP
Posts: 1212
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

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

Post by 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
Posts: 106
Joined: Mon Jul 29, 2019 5:02 am
OLAP Product: Planning Analytics
Version: 2.0.9.x
Excel Version: Office 365 16

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

Post by JohnO »

gtonkin wrote: Thu May 23, 2024 6:52 am 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.
User avatar
gtonkin
MVP
Posts: 1212
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

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

Post by gtonkin »

Awesome! Glad it worked out for you.

So much to learn on MDX still but it is really powerful stuff.
Wim Gielis
MVP
Posts: 3129
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis »

JohnO wrote: Sat May 25, 2024 7:35 am 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.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
JohnO
Posts: 106
Joined: Mon Jul 29, 2019 5:02 am
OLAP Product: Planning Analytics
Version: 2.0.9.x
Excel Version: Office 365 16

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

Post by JohnO »

Wim Gielis wrote: Sat May 25, 2024 5:43 pm Therefore, let's add in ";" in front of and after the search string (the cube value):

Even better.

Thanks Wim
Post Reply