Filtered by string measure in a 3 dimension cube

Post Reply
yyround
Posts: 17
Joined: Tue Apr 18, 2017 1:20 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: MS 2010

Filtered by string measure in a 3 dimension cube

Post by yyround » 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

User avatar
gtonkin
MVP
Posts: 593
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Filtered by string measure in a 3 dimension cube

Post by gtonkin » Tue May 15, 2018 5:31 am

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.

User avatar
Steve Rowe
Site Admin
Posts: 1765
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Filtered by string measure in a 3 dimension cube

Post by Steve Rowe » 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.

Wim Gielis
MVP
Posts: 1691
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Filtered by string measure in a 3 dimension cube

Post by Wim Gielis » Tue May 15, 2018 8:26 am

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.
Hi Steve,

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

User avatar
Steve Rowe
Site Admin
Posts: 1765
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Filtered by string measure in a 3 dimension cube

Post by Steve Rowe » 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,

User avatar
PavoGa
Community Contributor
Posts: 189
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 fixpack 7
Excel Version: 2013
Location: Cleveland, Tennessee

Re: Filtered by string measure in a 3 dimension cube

Post by PavoGa » Tue May 15, 2018 12:32 pm

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
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.

Code: Select all

GENERATE( TM1FILTERBYLEVEL( TM1SUBSETALL( [Dim2] ), 0),
     FILTER( TM1FILTERBYLEVEL( TM1SUBSETALL( [DIM1] ), 0),
          INSTR( [Cube].([Dim2].currentmember, [DimMeasure].[strMeasure], "AAA")) > 0))
BTW, one does not have to use braces (curly brackets) when the function returns a SET. They are needed only when needing to return a SET and the subject is a member of the dimension. Keeps the coding a little cleaner.
Ty
Cleveland, TN

yyround
Posts: 17
Joined: Tue Apr 18, 2017 1:20 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: MS 2010

Re: Filtered by string measure in a 3 dimension cube

Post by yyround » Wed May 16, 2018 11:20 pm

Thank you all for your help.

Much appreciated!

YY

Wim Gielis
MVP
Posts: 1691
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Filtered by string measure in a 3 dimension cube

Post by Wim Gielis » Tue May 22, 2018 9:21 am

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,
Hello Steve,

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

User avatar
Steve Rowe
Site Admin
Posts: 1765
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Filtered by string measure in a 3 dimension cube

Post by Steve Rowe » Tue May 22, 2018 9:53 am

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
MDX filter record.gif
MDX filter record.gif (17.03 KiB) Viewed 327 times
MDX filter record 2.gif
MDX filter record 2.gif (28.48 KiB) Viewed 327 times

MGrain
Posts: 8
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

Post by MGrain » Tue May 22, 2018 12:20 pm

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

Wim Gielis
MVP
Posts: 1691
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Filtered by string measure in a 3 dimension cube

Post by Wim Gielis » Tue May 22, 2018 1:20 pm

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).
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

User avatar
Steve Rowe
Site Admin
Posts: 1765
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Filtered by string measure in a 3 dimension cube

Post by Steve Rowe » Tue May 22, 2018 4:41 pm

Ohhh that "FIlter by View Extract" :oops:

That doesn't work for me either! You can get pretty close with the ordinary filter button though....

Wim Gielis
MVP
Posts: 1691
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Filtered by string measure in a 3 dimension cube

Post by Wim Gielis » Tue May 22, 2018 9:38 pm

Indeed Steve
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

Post Reply