Export (0) Print
Expand All
Avg
IIf
Lag
Max
Min
Mtd
Qtd
Sum
Var
Wtd
Ytd
Expand Minimize

Count (Set) (MDX)

Updated: 17 July 2006

Returns the number of cells in a set.


Standard syntax
Count(Set_Expression [ , ( EXCLUDEEMPTY | INCLUDEEMPTY ) ] )

Alternate syntax
Set_Expression.Count

Set_Expression

A valid Multidimensional Expressions (MDX) expression that returns a set.

The Count (Set) function includes or excludes empty cells, depending on the syntax used. If the standard syntax is used, empty cells can be excluded or included by using the EXCLUDEEMPTY or INCLUDEEMPTY flags, respectively. If the alternate syntax is used, the function always includes empty cells.

To exclude empty cells in the count of a set, use the standard syntax and the optional EXCLUDEEMPTY flag.

ms144823.note(en-US,SQL.90).gifNote:
The Count (Set) function counts empty cells by default. In contrast, the Count function in OLE DB that counts a set excludes empty cells by default.

Examples

The following example counts the number of cells in the set of members that consist of the children of the Model Name attribute hierarchy in the Product dimension.

WITH MEMBER measures.X AS
   [Product].[Model Name].children.count 
SELECT Measures.X ON 0
FROM [Adventure Works]

The following example counts the number of products in the Product dimension by using the DrilldownLevel function in conjunction with the Count function.

Count(DrilldownLevel ( 
   [Product].[Product].[Product]))

The following example returns those resellers with declining sales compared to the previous calendar quarter, by using the Count function in conjunction with the Filter function and a number of other functions. This query uses the Aggregate function to support the selection of multiple geography members, such as for selection from within a drop-down list in a client application.

WITH MEMBER Measures.[Declining Reseller Sales] AS
   Count
   (Filter
      (Existing(Reseller.Reseller.Reseller),
         [Measures].[Reseller Sales Amount] 
         < ([Measures].[Reseller Sales Amount],
            [Date].Calendar.PrevMember)
      )
   )
MEMBER [Geography].[State-Province].x AS 
   Aggregate
   ( {[Geography].[State-Province].&[WA]&[US], 
      [Geography].[State-Province].&[OR]&[US] } 
   )
SELECT NON EMPTY HIERARCHIZE 
   (AddCalculatedMembers 
      ({DrillDownLevel
         ({[Product].[All Products]})
      })
   ) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS 
FROM [Adventure Works]
WHERE ([Geography].[State-Province].x,
   [Date].[Calendar].[Calendar Quarter].&[2003]&[4]
   ,[Measures].[Declining Reseller Sales])

Release History

17 July 2006

Changed content:
  • Updated syntax and arguments to improve clarity.
  • Added updated examples.
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft