Returns: A set.
Syntax
>>-TopCount--(--SetExpression--,--CountExpression--+--------------------------+--)->< '-,--+-NumericExpression-+-' '-StringExpression--'
Parameters
- SetExpression
- Specifies the set whose greatest values are to be found.
- Count
- Specifies the number of tuples to return.
- NumericExpression
- Specifies a numeric expression that, when applied to each tuple in the set, gives the value by which the set is to be sorted. This expression is typically a measure. If you omit both this parameter and StringExpression, the function assumes the set to be in natural order.
- StringExpression
- Specifies an expression that resolves to a string representation of a number. When applied to each tuple in the set, the expression gives the value by which the set is to be sorted. This expression is typically a measure. If you omit both this parameter and NumericExpression, the function assumes the set to be in natural order.
Description
The TopCount function always breaks the hierarchy; that is, the members of the set are always sorted without regard to their positions in the hierarchy.
If there are duplicate values in the specified set, and there are more values in the specified set than the number specified by Count, then not all of the duplicates are returned.
Example
The following example uses the TopCount function to retrieve the profit amount for the three stores that have the largest profit amounts.
SELECT {[Price Analysis].[Measures].[Profit Amount]} ON AXIS(0)
, {TopCount(Descendants([Price Analysis].[Store].[All Stores], -1, LEAVES),
3, [Price Analysis].[Measures].[Profit Amount])} ON AXIS(1)
FROM [Price Analysis]| Store | Profit Amount |
|---|---|
| ValueTrend Store 1199 | 84813.24 |
| ValueTrend Store 116 | 81950.42 |
| ValueTrend Store 1095 | 81673.1 |
Related functions
The TopCount function with the NumericExpression parameter and the StringExpression parameter omitted is equivalent to the Head function.
You can use the BottomCount function to find the smallest values in a set.
You can use the TopPercent function to find the largest values in a set that compose a certain percent of the total of the values of the set, or you can use the TopSum function to find the largest values in a set that compose a certain sum.
The TopCount function sorts sets in the same way that the Order function does with the BDESC flag specified: that is, the function breaks hierarchies and sorts in descending order.