Returns the highest 'count' members of the set. Members are evaluated using the 'scalar' expression.
Our example returns the 5 [Cities] with the highest [Amount].
Query
TopCount([Geography].[Geo].[City].Members, 5, [Measures].[Amount])
Result
{
[Geography].[Geo].[City].[New York],
[Geography].[Geo].[City].[Geneva],
[Geography].[Geo].[City].[Zurich],
[Geography].[Geo].[City].[Lausanne],
[Geography].[Geo].[City].[Paris]
}
Assertion : Cell Equals
Head(ORDER([Geography].[Geo].[City].Members,[Measures].[Amount], BDESC), 5)
Similar example as above but using a string as value to compare.
Our example returns the 5 [Cities] sorted by name.
Query
TopCount([Geography].[Geo].[City].Members, 5, [Geography].[Geo].currentmember.caption)
Result
{
[Geography].[Geo].[City].[Zurich],
[Geography].[Geo].[City].[Valencia],
[Geography].[Geo].[City].[Toronto],
[Geography].[Geo].[City].[San Francisco],
[Geography].[Geo].[City].[Quebec]
}
Assertion : Cell Equals
Head(ORDER([Geography].[Geo].[City].Members,[Geography].[Geo].currentmember.caption, BDESC), 5)
Without any numeric expression specified, the TopCount function is equivalent to the Head function.
Query
TopCount([Geography].[Geo].[Country].Members, 3)
Result
{
[Geography].[Geo].[Country].[Canada],
[Geography].[Geo].[Country].[Mexico],
[Geography].[Geo].[Country].[United States]
}
Assertion : Cell Equals
Head([Geography].[Geo].[Country].Members, 3)
Tuples/Members with empty cells are included in TopCount, they are considered as zero values.
Query
WITH
SET [Empty Countries] AS Filter( [Geography].[Geo].[Country].Members, IsEmpty( [Measures].[Amount] ))
MEMBER [Top Count] AS Count( TopCount( [Empty Countries], 1, [Measures].[Amount] ))
SELECT
[Top Count] ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
MEMBER [Top Count] AS 1
SELECT
[Top Count] ON 0
FROM
[Sales]
Returns the highest 'count' tuples of the set. Tuples are evaluated using the 'numeric' expression.
Our example returns the 3 [Countries] for [2010] with the lowest [Amount].
Query
TopCount( NonEmpty( [Time].[Calendar].[2010] * [Geography].[Geo].[Country].Members ), 3, [Measures].[Amount] )
Result
{
( [Time].[Calendar].[Year].[2010], [Geography].[Geo].[Country].[United States] ),
( [Time].[Calendar].[Year].[2010], [Geography].[Geo].[Country].[Switzerland] ),
( [Time].[Calendar].[Year].[2010], [Geography].[Geo].[Country].[France] )
}
Assertion : MDX Equals
Head( Order( NonEmpty( [Time].[Calendar].[2010] * [Geography].[Geo].[Country].Members ), [Measures].[Amount], BDESC), 3)
TopCount makes no difference with duplicated members. Our example is selecting twice the top city for [Measures].[Amount].
Query
TopCount( Union( [Geography].[Geo].[City].Members, [Geography].[Geo].[City].Members, ALL), 2, [Measures].[Amount] )
Result
{
[Geography].[Geo].[City].[New York],
[Geography].[Geo].[City].[New York]
}
Assertion : Cell Equals
{
TopCount( [Geography].[Geo].[City].Members, 1, [Measures].[Amount] ),
TopCount( [Geography].[Geo].[City].Members, 1, [Measures].[Amount] )
}