Skip to main content

COUNT (MDX)

Returns the count of elements in the given set.

Returned Type

This function returns a number.

Syntax and Details

``````COUNT(set_expression)
``````

Or:

``````COUNT(set_expression,EXCLUDEEMPTY)
``````

By default, COUNT considers any empty elements and counts them along with the non-empty elements. If you use the EXCLUDEEMPTY keyword, this function returns the number of non-empty elements.

Example

For example, the following query counts the members of the Home City level:

``````SELECT COUNT(homed.city.MEMBERS) ON 0 FROM patients

COUNT
Results                                  9
``````

The next examples demonstrate the EXCLUDEEMPTY keyword. First, consider the following query:

``````SELECT aged.[age group].MEMBERS ON 0, diagd.MEMBERS ON 1 FROM patients WHERE MEASURES.[%COUNT]

0 to 29           30 to 59                60+
1 None                        3,839              3,615                971
2 asthma                        308                282                113
3 CHD                             1                 93                229
4 diabetes                       30                246                228
5 osteoporosis                    *                  *                200
``````

The following query counts the number of members of the Diagnoses level:

``````WITH SET myset AS 'diagd.MEMBERS'
SELECT COUNT(myset) ON 0 FROM patients
COUNT
All Patients                              5
``````

The following query counts the number of members of the Diagnoses level and uses the WHERE clause to get only patients in the age group 0 to 29:

``````WITH SET myset AS 'diagd.MEMBERS'
SELECT COUNT(myset) ON 0 FROM patients WHERE aged.[0 to 29]

COUNT
5
``````

As you can see, although the query uses the WHERE clause, the COUNT function returns the same value as before; this is because COUNT considers empty elements by default.

The next query is a variation of the preceding but uses EXCLUDEEMPTY:

``````WITH SET myset AS 'diagd.MEMBERS' SELECT COUNT(myset,EXCLUDEEMPTY) ON 0 FROM patients WHERE aged.[0 to 29]

COUNT
4
``````

For another example, you can use COUNT with a set of scalar items, rather than the more common set of members:

``````WITH SET test AS '{"item 1","item 2",23}'
SELECT COUNT(test) ON 0 FROM patients                                     COUNT
All Patients                              3
``````
FeedbackOpens in a new tab