MicroStrategy ONE

Count

The count function is usually used with an attribute, although a fact can also be counted. Metrics that use the Count function in their definition are commonly referred to as count metrics.

By default, the Count function counts the total number of attribute elements available in the data warehouse. However, this may not always be useful for analysis, because the function will return the same value regardless of the objects you place on a report's template.

You wish to find out how many customers made purchases in each category. You can use the Count of Customers metric, which uses the formula Count(Customer). The formula counts the number of elements in the Customer lookup table, and will always have the value 10,000, as in the report shown below:

To use the Count function for such an analysis, you must change the function parameters to count the attribute elements based on a fact, rather than the lookup table.

Change the Function Parameters for the Count Function

  1. Open the metric in the Metric Editor.
  2. In the Metric Definition pane, click the Count function once to highlight it.
  3. Right-click the highlighted Count function and choose Count parameters.
  4. Select whether to count distinct occurrences of the target using the Distinct drop-down list.

    A single customer may have made several orders. If you select False, the customer is counted once for each order. If you choose True, each customer is counted once only, irrespective of how many orders they have made.

  5. Select a fact you wish to use for the analysis under the FactID drop-down.

  6. Click OK.
  7. Click Save and Close.

The metric can now be used for various analyses, such as in the previous example, where you need a report that shows the number of customers that bought products in each category. The image below shows the same report, using the newly defined metric: