Select by Level, Regular Expression (Pattern) and Ordinal

Selecting members based on their level in the dimension hierarchy (TM1FilterByLevel) or by a pattern of strings in their name (TM1FilterByPattern) can be seen easily by using the Record Expression feature in the subset editor.

The classic “all leaf members” query using TM1’s level filtering command TM1FilterByLevel:
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}
Select all the leaf members which match the wildcard ‘*HC??’ – i.e. that have H and C as the third and fourth characters from the end of their name.
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}, "*HC??")}

The reason that these functions start with “TM1” is that they are not standard MDX commands and are unique to TM1. There are two main reasons why Applix will implement such unique functions: to add a feature that is present in “standard” TM1 and users will miss if it is not there; or because “standard” TM1 has the same feature as MDX but has historically implemented it slightly differently to MDX and therefore would, again, cause users problems if it was only implemented in the standard MDX way.

In these two cases, TM1FilterByPattern brings in a function commonly used by TM1 users that is lacking in MDX, while TM1FilterByLevel exists because TM1 has, since its launch in 1984, numbered consolidation levels starting at zero for the leaf level rising up the levels to the total members, while Microsoft decided to do it the exact opposite way.

In certain situations it is useful to use the standard MDX levels method and this is also available with the Levels function. It allows you return the members of a dimension that reside at the same level as a named member, just bear in mind that standard MDX orders the levels in terms of their distance from the top of the hierarchy and not the bottom as TM1.

This example returns all the members at the same level as the Retail member:
{ {[Product].[Retail].Level.Members} }
Which, although Retail is a high level consolidation, returns an N: item (Product Not Applicable) in the dimension because this rolls straight up into All Products as does Retail so they are considered to be at the same level.

To filter the dimension based on a level number you need to use the .Ordinal function. This is not documented as being supported in the Help file, and did not work in 8.2.7, but appears to work in 9.0 SP3 and 9.1.1.36 at least.
This example returns all the members at Level 1:
{Filter( {TM1SUBSETALL( [Product] )}, [Product].CurrentMember.Level.Ordinal = 1)}

This example would return all members not at the same level as Discount Loan.
{Filter( {TM1SUBSETALL( [Product] )}, [Product].CurrentMember.Level.Ordinal <> [Product].[Discount Loan].Level.Ordinal)}