Working with Leaf Members in a Parent-child hierarchy using MDX

By - May 28, 2015

When assigning values to leaf members in a parent-child hierarchy, please be aware of the scoping method.

Let’s take a look at an example.  Below is an MDX rule within a Kepion model.  In this rule, although the definition for Y1 and Y2 member are different, we expect the results to be the same because function DESCENDANTS([Account].[Account].[Income Statement], 1000, LEAVES) and [Account].[Account].Properties(“ChildCount”) = “0” should return the same member set.

MDX Editor

However, the results we get are different, see the form below.

Parent-Child Hierarchy

The difference comes from the data members in the parent-child hierarchy.  If merely scoping the leaves using DESCEDANTS MDX function, the data member of the parent member will be included in the result set.  Here is the Data Member definition.

A data member is a child member associated with a parent member in a parent-child hierarchy.  A data member contains the data value for its parent member, rather than the aggregated value for the parent’s children.

If your intention is to work with the member leaf only, you can use Kepion-generated attribute “ChildCount”.  Please refer to the syntax in the MDX rule example above.