DESCENDANTS

The DESCENDANTS identifier returns all the children's values (till the leaf level) as an array. It needs to be used with an aggregate function to return values.

Example

Let us consider the scenario where we have Category, Subcategory and Region in the row hierarchy, Quarter in columns and Profit and Sales as measures. The minimum sales and profit across regions and subcategories can be calculated using the formula as shown in the below image.

Using the DESCENDANTS identifier