Count

Count returns the number of elements in a list of values. This is a group-value function.

Syntax

Count<Distinct, FactID, UseLookupForAttributes, Null> (Argument)

Where:

• Argument is an attribute, fact, or metric representing a list of values.
• Distinct is a TRUE/FALSE parameter that allows you to count all elements in a list or only the unique elements.
• FactID is a parameter that forces a calculation to take place on a fact table that contains the selected fact.
• UseLookupForAttributes is a TRUE/FALSE parameter that can be used when performing an aggregation of an attribute:
  • If you set this parameter to TRUE, the aggregation is done for the unique set of attribute elements, excluding any duplicates caused by additional attributes on the report.
  • If you set this parameter to FALSE, the aggregation is done for all elements of an attribute, including duplicate elements that can be included by displaying additional attributes on a report.

For an example of how to use this parameter, see Performing Counts of Attributes.

Example

This example creates a report that calculates the count of employees in each region. The report contains the attributes Region and Employee, and the metric # of Employees. A report filter limits the regions displayed to Central, South, Southeast, and Southwest. The metric # of Employees is defined as follows: Count(Employee){Region, ~}.

The function counts each entry within a region and returns the last number in the count.

Performing Counts of Attributes

While most functions are commonly used with facts and metrics, the Count function is commonly used to count the number of elements for an attribute. For example, the report shown below displays the customer revenue for each item they purchased.

This report also uses two derived metrics to display the following count information:

• Number of Customers: This derived metric displays the count of customers. The expression used to define the derived metric is Count<UseLookupForAttributes=True>(Customer){}. By defining the parameter UseLookupForAttributes=True, the Count function ignores any multiple listings of each Customer attribute element. In the report shown above, each separate item is ignored and only the unique attribute elements for Customer are counted.
• Number of Customer Purchases: This derived metric displays the count of customer purchases. The expression used to define the derived metric is Count<UseLookupForAttributes=False>(Customer){}. By defining the parameter UseLookupForAttributes=False, the Count function counts each listing of the Customer attribute element. In the report shown above, each separate item is counted, which provides a count of customer purchases.

For information on creating derived metrics, along with other OLAP Services features, see the In-memory Analytics Guide.