TM1 Forum

Hi Expert,
I am using an active form. I need to filtrate my first column with a value.
I created an MDX to do that and it works perfectly:
example:
{TM1FILTERBYPATTERN( {TM1DRILLDOWNMEMBER( {TM1SUBSETALL( [Classe] )}, ALL, RECURSIVE )}, '*am*')}
This will give me all the classes that contains AM

Now, I want the user to be able to enter am in a CELL (say $B$10).
How do I replace the '*am*' with the value in B10.

Thanks a million.
Micheline

PS: I tried the following as suggested in another chat, but it did not work
{TM1FILTERBYPATTERN( {TM1DRILLDOWNMEMBER( {TM1SUBSETALL( [Classe] )}, ALL, RECURSIVE )}, ' " &$B$10& " ' ) }

Correction.
I tried

{TM1FILTERBYPATTERN( {TM1DRILLDOWNMEMBER( {TM1SUBSETALL( [Classe] )}, ALL, RECURSIVE )}, ' " &$B$10& " ' ) }

in a different environment (I think excel is newer) it worked.
Lesson learnt.

thanks
Micheline

Hi,

Just for info, I have moved away from using TM1FILTERBYPATTERN because it can't handle aliases.

The following topics describe the issues, and the workarounds using the INSTR function.

regards,

Mark

Mark RMBC wrote: ↑Fri Jul 01, 2022 9:34 am Hi,

Just for info, I have moved away from using TM1FILTERBYPATTERN because it can't handle aliases.

The following topics describe the issues, and the workarounds using the INSTR function.

regards,

Mark

TM1FILTERBYPATTERN can handle aliases. Provide the alias name as a third argument within double quotes. I only found out last week and it seems this was new info to the community.

Cheers Wim, I wasn't aware of the mystery 3rd parameter, and useful to know.

I should have read George Tonkin's MDX guide a bit more closely! That one went right over my head.

3rd parameter is in my guide only because Wim found it!