TM1 Forum

Posted: **Tue Sep 18, 2018 9:56 pm**

Hello,
I am trying to write MDX that would return leaf elements for multiple consolidations. For example,
{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [Period] )}, "M01")}, ALL, RECURSIVE )}, 0)}
will return all dates under M01 (Month1). Is there a way to write MDX so it would return all dates for M01, M02 and M03 without using UNION?

Posted: **Wed Sep 19, 2018 12:07 am**

In your MDX example, TM1FILTERBYPATTERN is not doing anything, since you don't have a wildcard (*) in your criteria.

If what you wanted is just to specify, months (M01 to M12), then bring through all the children/leaf elements, then this may help:

Code: Select all

{TM1FILTERBYLEVEL(
	{DESCENDANTS(
		{[Period].[M01],[Period].[M02],[Period].[M03],[Period].[M04],[Period].[M05],[Period].[M06]}
	)},
0)}

DESCENDANTS is a substitute for TM1DRILLDOWNMEMBER( {} , ALL RECURSIVE).

But if filtering is required, then the code is a little bit more complicate:

Code: Select all

{TM1FILTERBYLEVEL(
	{DESCENDANTS(
		{FILTER(
			{TM1SUBSETALL([Period])},
			LEFT([Period].CurrentMember.Name,1)= "M" or LEFT([Period].CurrentMember.Name,2)= "M0")},
				)},
0)}

Posted: **Wed Sep 19, 2018 4:40 pm**

This is perfect, thank you!

TM1 Forum

MDX Question

MDX Question

Re: MDX Question

Re: MDX Question