Count (Set) (MDX)

Returns the number of cells in a set.

Syntax

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

Arguments

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

Remarks

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.

Note

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])  
  

See Also

Count (Dimension) (MDX)
Count (Hierarchy Levels) (MDX)
Count (Tuple) (MDX)
DrilldownLevel (MDX)
AddCalculatedMembers (MDX)
Hierarchize (MDX)
Properties (MDX)
Aggregate (MDX)
Filter (MDX)
PrevMember (MDX)
MDX Function Reference (MDX)