TM1 Forum

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.

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

GENERATE( TM1FILTERBYLEVEL( TM1SUBSETALL( [Dim2] ), 0),
     FILTER( TM1FILTERBYLEVEL( TM1SUBSETALL( [DIM1] ), 0),
          INSTR( [Cube].([Dim2].currentmember, [DimMeasure].[strMeasure], "AAA")) > 0))

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,