Filtered by string measure in a 3 dimension cube
-
- Posts: 27
- Joined: Tue Apr 18, 2017 1:20 am
- OLAP Product: TM1
- Version: PA2.0.8
- Excel Version: MS 2016
Filtered by string measure in a 3 dimension cube
Hi all,
I am wondering whether anyone has come across a situation like this:
There is Cube with 3 dimensionS, Dim1, Dim2, DimMeasure. There is a string Measure element under DimMeasure lets called it StrMeasure. StringMeasure is only recorded in the N level elements of Dim1 and Dim2.
Now I want to list all the Dim1 elements (all N level of course) that has a StrMeasure as "AAA".
The following MDX wont do the job, why and how to correct it??
{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Dim1] )}, 0)},[Cube].([DimMeasure].[StrMeasure]) = "AAA" )}
If want to take the query further to filter the stringMeasure to return all those has "AAA" as prefix, can we do the query with combination of FILTER AND TM1FILTERBYPATTERN?? Has anyone ever done that?
Many thanks for your time.
YY
I am wondering whether anyone has come across a situation like this:
There is Cube with 3 dimensionS, Dim1, Dim2, DimMeasure. There is a string Measure element under DimMeasure lets called it StrMeasure. StringMeasure is only recorded in the N level elements of Dim1 and Dim2.
Now I want to list all the Dim1 elements (all N level of course) that has a StrMeasure as "AAA".
The following MDX wont do the job, why and how to correct it??
{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Dim1] )}, 0)},[Cube].([DimMeasure].[StrMeasure]) = "AAA" )}
If want to take the query further to filter the stringMeasure to return all those has "AAA" as prefix, can we do the query with combination of FILTER AND TM1FILTERBYPATTERN?? Has anyone ever done that?
Many thanks for your time.
YY
- gtonkin
- MVP
- Posts: 1199
- 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: Filtered by string measure in a 3 dimension cube
Your "AAA" string is stored in a cube so filtering by pattern will not work. Have a look around on the forum for where INSTR is used in MDX as you would need to use that to do your wildcard match.
- Steve Rowe
- Site Admin
- Posts: 2417
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Filtered by string measure in a 3 dimension cube
If you still have access to the Perspectives subset editor then you can record the MDX for what you want by turning the recorder on and then using the filter dialogue.
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- MVP
- Posts: 3118
- 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: Filtered by string measure in a 3 dimension cube
Hi Steve,Steve Rowe wrote: ↑Tue May 15, 2018 7:53 am If you still have access to the Perspectives subset editor then you can record the MDX for what you want by turning the recorder on and then using the filter dialogue.
AFAIK you cannot record an expression when you filter on view extract. The icon is greyed out. Or did you mean something else ?
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
- Steve Rowe
- Site Admin
- Posts: 2417
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Filtered by string measure in a 3 dimension cube
Hi Wim,
I do this a lot to get the base form of the MDX that I want to use in active forms or elsewhere so it is very much possible.
Maybe one of those things that was not available on first release and you've not revisited? That happens to me, small change that I've not picked up on, or maybe just a failing memory...
Cheers,
I do this a lot to get the base form of the MDX that I want to use in active forms or elsewhere so it is very much possible.
Maybe one of those things that was not available on first release and you've not revisited? That happens to me, small change that I've not picked up on, or maybe just a failing memory...
Cheers,
Technical Director
www.infocat.co.uk
www.infocat.co.uk
- PavoGa
- MVP
- Posts: 617
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: Filtered by string measure in a 3 dimension cube
If you expect this to function properly in a cube view, Dim2 will have to be a context (or title) dimension. If this MDX is to retrieve all Dim1 elements where strMeasure = 'AAA' along the Dim1/Dim2 intersections on its own (say, for building a subset within a TI), then you'll need to use GENERATE on Dim2.yyround wrote: ↑Mon May 14, 2018 11:27 pm Hi all,
I am wondering whether anyone has come across a situation like this:
There is Cube with 3 dimensionS, Dim1, Dim2, DimMeasure. There is a string Measure element under DimMeasure lets called it StrMeasure. StringMeasure is only recorded in the N level elements of Dim1 and Dim2.
Now I want to list all the Dim1 elements (all N level of course) that has a StrMeasure as "AAA".
The following MDX wont do the job, why and how to correct it??
{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Dim1] )}, 0)},[Cube].([DimMeasure].[StrMeasure]) = "AAA" )}
If want to take the query further to filter the stringMeasure to return all those has "AAA" as prefix, can we do the query with combination of FILTER AND TM1FILTERBYPATTERN?? Has anyone ever done that?
Many thanks for your time.
YY
Code: Select all
GENERATE( TM1FILTERBYLEVEL( TM1SUBSETALL( [Dim2] ), 0),
FILTER( TM1FILTERBYLEVEL( TM1SUBSETALL( [DIM1] ), 0),
INSTR( [Cube].([Dim2].currentmember, [DimMeasure].[strMeasure], "AAA")) > 0))
Ty
Cleveland, TN
Cleveland, TN
-
- Posts: 27
- Joined: Tue Apr 18, 2017 1:20 am
- OLAP Product: TM1
- Version: PA2.0.8
- Excel Version: MS 2016
Re: Filtered by string measure in a 3 dimension cube
Thank you all for your help.
Much appreciated!
YY
Much appreciated!
YY
-
- MVP
- Posts: 3118
- 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: Filtered by string measure in a 3 dimension cube
Hello Steve,Steve Rowe wrote: ↑Tue May 15, 2018 12:29 pm Hi Wim,
I do this a lot to get the base form of the MDX that I want to use in active forms or elsewhere so it is very much possible.
Maybe one of those things that was not available on first release and you've not revisited? That happens to me, small change that I've not picked up on, or maybe just a failing memory...
Cheers,
I'm on PAL 2.0.4, good old Subset Editor.
I can record an expression with a filter on an attribute, for instance, but not on a view extract. This icon is greyed out when an expression is recorded.
Do you see something different ?
Wim
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
- Steve Rowe
- Site Admin
- Posts: 2417
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Filtered by string measure in a 3 dimension cube
Hi Wim,
See below, you can tell the recorder is on because the Save button is greyed out. I seem to get the same behaviour irrespective of how I access the subset editor or the release
See below, you can tell the recorder is on because the Save button is greyed out. I seem to get the same behaviour irrespective of how I access the subset editor or the release
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- Posts: 16
- Joined: Wed Nov 15, 2017 11:36 am
- OLAP Product: TM1
- Version: Various
- Excel Version: Various
Re: Filtered by string measure in a 3 dimension cube
Hi Steve
The Filter by View Extract icon is greyed out in your last screenshot...
I see the same behaviour as Wim, as I press Record Expresssion, that icon and the OK button grey out.
Cheers
Mike
The Filter by View Extract icon is greyed out in your last screenshot...
I see the same behaviour as Wim, as I press Record Expresssion, that icon and the OK button grey out.
Cheers
Mike
-
- MVP
- Posts: 3118
- 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: Filtered by string measure in a 3 dimension cube
I think we speak of different things.
There is Filter in the Tools menu, and Filter by view extract.
The first is possible when an expression is recorded (and can be useful), the second one is not enabled (too bad).
There is Filter in the Tools menu, and Filter by view extract.
The first is possible when an expression is recorded (and can be useful), the second one is not enabled (too bad).
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
- Steve Rowe
- Site Admin
- Posts: 2417
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Filtered by string measure in a 3 dimension cube
Ohhh that "FIlter by View Extract"
That doesn't work for me either! You can get pretty close with the ordinary filter button though....
That doesn't work for me either! You can get pretty close with the ordinary filter button though....
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- MVP
- Posts: 3118
- 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: Filtered by string measure in a 3 dimension cube
Indeed Steve
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