Finding the name of a child with the best/worst value

Sometimes, there is a need to perform a for-each loop to get the top or bottom members in the inner hierarchy for each member in the outer hierarchy. The Identifying the best/worst members for each member of another hierarchy recipe deals with exactly that kind of topic.

Following the theme of the recipes in this chapter of reducing the size of a report to a manageable level, in this recipe we will show you another possibility of how to reduce the size of the result – by showing not all the descendant members, but only the best child member. We will demonstrate how to identify the member with the best/worst value, only this time the member is not just from any other hierarchy, it will be from its children. We do not need the best/worst value from the child, and we are only going to return the name of the child in a calculated measure.

In our example, we will use the Product dimension. For every product subcategory, we are going to find...