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?
MDX - Filtering members against multiple string values in a reference cube
- 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
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.
Edit: Another option may be something like this:
This essentially starts with your Profit Centre list then for each Asset item, you could retrieve the Profit Centre and compare.
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
)
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")))
-
- Posts: 105
- 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
Thanks Georgegtonkin 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.
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
- 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
Awesome! Glad it worked out for you.
So much to learn on MDX still but it is really powerful stuff.
So much to learn on MDX still but it is really powerful stuff.
-
- MVP
- Posts: 3128
- 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
Thanks George and John.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.
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 )
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
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
-
- Posts: 105
- 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
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