Identifying Descendants with MDX? Use IsAncestor

There were many times when I found myself wondering why there wasn’t an ISDESC or IsDescendants function for MDX. Then one day I found the IsAncestor function. With this function, a descendants function is completely unnecessary, although it is a little counterintuitive when you are used to the BSO world of formulas.

The formatting of this function is: IsAncestor (member1, member2 [,INCLUDEMEMBER]).

The trick to this function is to ask yourself each time you use it “Is the first member the ancestor of the second member?”

For the examples, I will use the Sample ASO application and focus on the Market dimension.

So, if you wanted to see all of the descendants of East in the Market dimension your formula would be IsAncestor([East], [Market].CurrentMember) with the result set being New York, Massachusetts, Florida, Connecticut, and New Hampshire.

If you wanted to see all descendants of East in the Market dimension including East, your formula would be IsAncestor([East], [Market].CurrentMember, INCLUDEMEMBER) and your result set would be East, New York, Massachusetts, Florida, Connecticut, and New Hampshire.

You can also use the function to test and see if a member is a descendant of another member. An example of this would be IsAncestor([Market].CurrentMember, [Florida]). The result set would be true for the East and Market members and false for all other Market members. If you add INCLUDEMEMBER in the function your result set would also include the member Florida.

It would be far easier if there were a descendant function in MDX, but once you get used to using IsAncestor, you will find it will do the job every time.