Page 1 of 1
MDX - Filtering members against multiple string values in a reference cube
Posted: Wed May 22, 2024 11:30 pm
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?
Re: MDX - Filtering members against multiple string values in a reference cube
Posted: Thu May 23, 2024 6:52 am
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.
Re: MDX - Filtering members against multiple string values in a reference cube
Posted: Sat May 25, 2024 7:35 am
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.
Re: MDX - Filtering members against multiple string values in a reference cube
Posted: Sat May 25, 2024 9:25 am
by gtonkin
Awesome! Glad it worked out for you.
So much to learn on MDX still but it is really powerful stuff.
Re: MDX - Filtering members against multiple string values in a reference cube
Posted: Sat May 25, 2024 5:43 pm
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.
Re: MDX - Filtering members against multiple string values in a reference cube
Posted: Mon May 27, 2024 4:07 am
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