Returns the lowest 'count' members of the set. Members are evaluated using the 'numeric' expression.
In our example returns the 3 [Countries] with the lowest [Amount].
Query
BottomCount( NonEmpty( [Geography].[Geo].[Country].Members ), 3, [Measures].[Amount] )
Result
{
[Geography].[Geo].[Country].[Spain],
[Geography].[Geo].[Country].[France],
[Geography].[Geo].[Country].[Switzerland]
}
Assertion : MDX Equals
Head( Order( NonEmpty( [Geography].[Geo].[Country].Members ), [Measures].[Amount], BASC), 3)
Returns the lowest 'count' tuples of the set. Tuples are evaluated using the 'numeric' expression.
In our example returns the 3 [Countries] for [2010] with the lowest [Amount].
Query
BottomCount( NonEmpty( [Time].[Calendar].[2010] * [Geography].[Geo].[Country].Members ), 3, [Measures].[Amount] )
Result
{
( [Time].[Year].[2010], [Geography].[Geo].[Country].[Spain] ),
( [Time].[Year].[2010], [Geography].[Geo].[Country].[France] ),
( [Time].[Year].[2010], [Geography].[Geo].[Country].[Switzerland] )
}
Assertion : MDX Equals
Head( Order( NonEmpty( [Time].[Calendar].[2010] * [Geography].[Geo].[Country].Members ), [Measures].[Amount], BASC), 3)
Without any numeric expression specified, the BottomCount function is equivalent to the Tail function.
Query
BottomCount( [Geography].[Geo].[Country].Members, 3)
Result
{
[Geography].[Geo].[Country].[France],
[Geography].[Geo].[Country].[Spain],
[Geography].[Geo].[Country].[Switzerland]
}
Assertion : MDX Equals
Tail( [Geography].[Geo].[Country].Members, 3 )
Tuples/Members with empty cells are included in BottomCount, they are considered as zero values.
In order to check this behavior we will add a little complication :
[count +] : should return 0 as two empty countries are the lowest ones ([Amount] is positive)
[count -] : should return 2 as negative values are lower than empty ones.
Query
WITH
SET [Countries +] AS BottomCount( [Geography].[Geo].[Country].Members, 2, [Measures].[Amount] )
SET [Countries -] AS BottomCount( [Geography].[Geo].[Country].Members, 2, -[Measures].[Amount] )
MEMBER [count +] AS count([Countries +] * [Measures].[Amount] , EXCLUDEEMPTY)
MEMBER [count -] AS count([Countries -] * [Measures].[Amount] , EXCLUDEEMPTY)
SELECT
{[Measures].[count +],[Measures].[count -]} ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
MEMBER [count +] AS 0
MEMBER [count -] AS 2
SELECT
{[Measures].[count +],[Measures].[count -]} ON 0
FROM
[Sales]
BottomCount makes no difference with duplicated members. Our example is selecting twice the [Country] with the lowest [Amount].
Query
BottomCount( NonEmpty( Union( [Geography].[Geo].[Country].Members, [Geography].[Geo].[Country].Members, ALL )), 2, [Measures].[Amount] )
Result
{
[Geography].[Geo].[Country].[Spain],
[Geography].[Geo].[Country].[Spain]
}
Assertion : MDX Equals
{
BottomCount( NonEmpty( [Geography].[Geo].[Country].Members ), 1, [Measures].[Amount] ),
BottomCount( NonEmpty( [Geography].[Geo].[Country].Members ), 1, [Measures].[Amount] )
}