TM1 MDX: GET ALL THE ASCENDANTS OF SUBSET ELEMENTS

TM1 supports a standard MDX function ASCENDANTS which returns all the parent elements (up to the dimension root).

Let’s say you have a dimension like:
— World
—- Europe
—— UK
—— France
——– Paris
—- North America
—— USA
—— Canada
——– Toronto
—- South America
—— Brazil

You want to select {Toronto, Paris} and all the parent elements.
ASCENDANTS accepts only a member. To deal with a set use another standard MDX function GENERATE, which accepts element subset and executes some expression against each its element.

{GENERATE( {[dim_location].[Toronto],[dim_location].[Paris]}, ASCENDANTS( [}dim_location].CurrentMember ) )}

and it will return:
Toronto
Canada
World
Paris
France

You can also use HIERARCHIZE function to sort it by hierarchy:

{HIERARCHIZE( {GENERATE( {[dim_location].[Toronto],[dim_location].[Paris]}, ASCENDANTS( [}dim_location].CurrentMember ) )} )}

Recent Posts

Recent Comments

Meta

Written by:

3 Comments

  1. Vadym
    10/4/2016
    Reply

    thanks

  2. Hassan
    6/21/2018
    Reply

    I am trying DESCENDANT function which is similar to ascendant. It works fine however i want to exclude the parent items and only keep the children(Descendants)…is there a way to exclude parent items?

  3. Vlad
    6/25/2018
    Reply

    Hi Hassan. Using DESCENDANT function you will get all the child elements. If you want to exclude all the parents (consolidated elements), you can filter the result set by level 0. Also instead of GENERATE I suggest using TM1DRILLDOWNMEMBER function (which is “more stable”):
    {TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER( {[dimName].[parent1], [dimName].[parent2]}, ALL, RECURSIVE )}, 0)}

Leave a Reply

Your email address will not be published. Required fields are marked *