The Count function
returns the number of members in a specified set.
Returns: A number.
Syntax
.-,--INCLUDEEMPTY-. >>-Count--(--SetExpression--+-----------------+--)------------->< '-,--EXCLUDEEMPTY-'
Parameters
- SetExpression
- Specifies a set.
- EXCLUDEEMPTY
- Excludes the empty cells.
- INCLUDEEMPTY
- Includes the empty cells. If you do not specify an empty option, INCLUDEEMPTY is assumed.
Example
The following example uses the Count function to retrieve the number of stores that have declining sales compared to the previous month for the months of the year 2002.
Query
WITH
MEMBER [Price Analysis].[Measures].[Count of Stores with Declining Sales]
AS Count(Filter(Descendants([Price Analysis].[Store].[All Stores],
-1, LEAVES),
([Price Analysis].[Time].CurrentMember,
[Price Analysis].[Measures].[Sales Amount])
< ([Price Analysis].[Time].PrevMember,
[Price Analysis].[Measures].[Sales Amount])))
SELECT
{[Price Analysis].[Measures].[Count of Stores with Declining Sales]}
ON AXIS(0),
{Descendants([Price Analysis].[Time].[All Time (Calendar)].[2002], 2)}
ON AXIS(1)
FROM [Price Analysis]Result
| Time | Count of Stores with Declining Sales |
|---|---|
| 1 | 0 |
| 2 | 2 |
| 3 | 11 |
| 4 | 2 |
| 5 | 8 |
| 6 | 2 |
| 7 | 5 |
| 8 | 10 |
| 9 | 5 |
| 10 | 5 |
| 11 | 11 |
| 12 | 0 |
Related functions
The Count function is part of a family of aggregating functions that includes Aggregate, Avg, Sum, Min, and Max.
The Count dot function is similar to the Count function, but it can be applied either to all the levels of a specified hierarchy or to a set, and does not offer an ExcludeEmpty option.