Filtered by string measure in a 3 dimension cube

Post Reply
yyround
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

Post by yyround »

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: 1192
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

Post by gtonkin »

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: 2410
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

Post by Steve Rowe »

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
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

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

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
User avatar
Steve Rowe
Site Admin
Posts: 2410
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

Post by Steve Rowe »

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,
Technical Director
www.infocat.co.uk
User avatar
PavoGa
MVP
Posts: 616
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

Post by PavoGa »

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

Post by yyround »

Thank you all for your help.

Much appreciated!

YY
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

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

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
User avatar
Steve Rowe
Site Admin
Posts: 2410
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

Post by Steve Rowe »

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 6641 times
MDX filter record 2.gif
MDX filter record 2.gif (28.48 KiB) Viewed 6641 times
Technical Director
www.infocat.co.uk
MGrain
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

Post by MGrain »

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: 3105
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

Post by Wim Gielis »

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

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
User avatar
Steve Rowe
Site Admin
Posts: 2410
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

Post by Steve Rowe »

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....
Technical Director
www.infocat.co.uk
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

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
Post Reply