COUNT function returns the number of unique values of an attribute within the given context.


COUNT function can be used in the following three syntax forms:

single parameterSELECT COUNT(attribute)SELECT COUNT({attribute/order_id})
two parametersSELECT COUNT(attribute, primary_key)SELECT COUNT({attribute/order_id}, {attribute/campaign_id})
with USINGSELECT COUNT(attribute) USING primary_keySELECT COUNT({attribute/order_id}) USING {attribute/campaign_id}

Single-Parameter Version

The single-parameter version of COUNT dynamically gets the context of where to count from the insight it is used in.

Two-Parameter Version

In the two-parameter version, the context where to count the attribute is determined explicitly by the second parameter - the primary key of the dataset.

The primary key is connection point between datasets. It connects the COUNT function’s first parameter to the dataset in which the count is to take place.

Specifying COUNT Context Resolution with USING

You deploy the USING keyword in logical data models with ambiguous connection points.

The context for computation of COUNT may be ambiguous if there are multiple fact tables which relate to a counted attribute. Imagine a model with fact datasets Purchase_Fact and Sales_Fact that are both connected to the Store and Product attributes.

model for count example

If you build an insight that displays the count of products per store with SELECT COUNT({attribute/product}) and slice it with Store, it would not compute because it is ambiguous if the insight displays the number of unique products that have been purchased by store or the number of unique products that have been sold by the store.

In a metric, USING provides a hint for which context should be used. For example the insight will show the number of unique purchased products per store if Purchase_Fact attribute is placed into the USING clause SELECT COUNT({attribute/product})USING {fact/purchase_fact}.

The attribute in the USING clause does not need to be from the actual fact table, it can also be another attribute which uniquely determines the correct context (e.g. use of the Purchase Date attribute in the USING clause directs the use of the Purchase_Fact dataset to join Product with Store because Sales_Fact does not directly relate to Purchase Date).