Skip navigation.
spilling the beans

Filtering by CurrentMember, NextMember, PrevMember, Ancestor and FirstSibling

developers
| |

This example returns the members that have an Amount value in the Test cube above 18. The [Product].CurrentMember part is optional here but it makes the next example clearer.
{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)},
[Test].([Product].CurrentMember, [Posting Measures].[Amount]) > 18 )}

This query then modifies the previous query slightly to return members where the NEXT member in the dimension has a value above 18. In practice this is probably more useful in time dimensions.
{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)},
[Test].([Product].CurrentMember.NextMember, [Posting Measures].[Amount]) > 18 )}

This can then be improved to returning members where the next member is greater than their amount.
{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)},
[Test].([Product].CurrentMember.NextMember, [Posting Measures].[Amount]) >
[Test].([Product].CurrentMember, [Posting Measures].[Amount]) )}

In addition to NextMember, PrevMember can also be used as could lags and leads.

The simple, but unsupported as of 9.1.1.89, Name function allows you to filter according to the name of the member. As well as exact matches you could find exceptions, ‘less-thans’ and ‘greater-thans’, bearing in mind these are alphanumeric comparisons not data values.

This example returns all base members before and including the last day in January 1972.
{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL([Date])} ,0)},
[Date].CurrentMember.Name
For example, this could be a useful query even a dimension not as obviously sorted as dates are:
{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL([Product])} ,0)},
[Product].CurrentMember.Name
which returns all base members before MidasJ in terms of their name rather than their dimension index.

Parent returns the first parent of a given member:
{ [Product].[Customer Lending].Parent }

Used with Filter you can come up with another way of doing a “children of” query:
{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL([Date])} ,0)},
[Date].CurrentMember.Parent.Name = "1972 - January")}

Ancestor() can be used instead of Parent if desired. This example returns base-level product members whose first parents have a value above zero, in other words a kind of family-based suppress zeroes: a particular product might have a value of zero but if one if its siblings has a value then it will still be returned.
{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}, [Test].(Ancestor([Product].CurrentMember,0), [Posting Measures].[Amount]) > 0 )}

This example filters the products based on whether they match the Amount value of MidasHCBK.
{FILTER(
{TM1SUBSETALL( [Product] )}, [Test].(Ancestor([Product].CurrentMember,0), [Posting Measures].[Amount]) = [Test].([Product].[MidasHCBK], [Posting Measures].[Amount])
)}

This example uses FirstSibling to filter the list based on whether a product’s value does not match that products’ First Sibling (useful for reporting changing stock levels or employee counts over time, for example, things that are usually consistent).
{FILTER( {TM1FilterByLevel({TM1SUBSETALL( [Product] )}, 0)}, [Test].(Ancestor([Product].CurrentMember,0), [Posting Measures].[Amount]) <> [Test].([Product].CurrentMember.FirstSibling, [Posting Measures].[Amount]) )}


Average rating
(1 vote)