University > Looker Studio Course > Formulas > Looker Studio function : COUNT_DISTINCT

Looker Studio function : COUNT_DISTINCT

Mastering Looker Studio (formerly Google Data Studio) becomes much easier once you understand its core functionalities and capabilities. One such functionality is the COUNT_DISTINCT function. As the name suggests, this function is designed to count the number of unique items present in a specific field. Whether you're managing a marketing analytics operation or exploring a vast database, the COUNT_DISTINCT function proves invaluable in presenting clear, digestible data to your viewers.

The Syntax of the Function

The syntax of the COUNT_DISTINCT function is straight forward:


COUNT_DISTINCT(value)

Here, "value" denotes the field or expression whose unique items you wish to count. The function will then return the total number of unique items within that specified field or expression.

How the Function Works

The COUNT_DISTINCT function serves as a filtering tool, disregarding duplicates and counting only unique instances within a field. Its functionality can be applied in one of two ways:

In the data source, you can modify a field's Aggregation type to Count Distinct.
While working on a report, you can tweak the field's aggregation in a chart.

In essence, the function is used to count unique elements, not duplicate entries.

Examples of the Function

Now, let's go over some practical examples to understand the application of the COUNT_DISTINCT function better. Suppose you run an e-commerce business, and you want to evaluate the performance of your products. You have a field denoting 'Product Sold,' and you want to find out how many unique products were sold.

You would use the function as follows:


COUNT_DISTINCT(Product Sold)

This function would then return the count of distinct products sold, providing a clear metric for product diversification.

Limitations of the Function

The COUNT_DISTINCT function cannot be applied to a field that is pre-aggregated (Aggregation type of Auto) or to an expression which has already undergone another aggregation function. For instance, an expression like COUNT_DISTINCT(Sessions) in a Google Analytics data source will result in an error.

Another critical mention is that while using BigQuery data sources, this function might lead to higher query costs; hence, using APPROX_COUNT_DISTINCT is suggested instead in some cases.

Tips to Utilize the Function Optimally

Differentiating the COUNT_DISTINCT function from the COUNT function is essential. The former returns the number of unique values, while the latter counts all entries, including duplicates.
Be cautious with the data sources, selecting dimensions before establishing aggregations. Using the COUNT_DISTINCT function on an already aggregated field can lead to error messages.
Remember to evaluate your Google Analytics data considering the possible cost implications of distinct count calculations.

The COUNT_DISTINCT function is one method to uncover meaningful insights from your data. It helps interpret data accurately and effectively, empowering your business to make more informed decisions. Remember to use this functionality strategically, as part of a broader, comprehensive approach to data analytics.

‍

More function to use with Looker Studio

COALESCE

Looker Studio function : COALESCE

MIN

Mastering the MIN Function in Looker Studio: A Comprehensive Guide on Data Analysis and Visualization Tools

REGEXP_EXTRACT

Mastering REGEXP_EXTRACT Function in Looker Studio: A Comprehensive Guide for Effective Data Extraction and Manipulation

ROUND

Understanding and Effectively Using the ROUND Function in Looker Studio: An Essential Data Analysis Tool

LEFT_TEXT

Understanding and Utilizing the LEFT_TEXT Function in Looker Studio for Effective Data Analysis

Become a Looker Studio expert
for free

Learn all the secrets of data analysis and create beautiful and effective dashboards thanks to our 30-second video courses! Join Catchr's community on YouTube.

Get more video lessons