Amazon QuickSight – distinctCountOver

The distinctCountOver function in Amazon QuickSight is used to calculate the number of unique values in a specified field, based on a defined partition and calculation level. It returns the distinct count of the specified field over the partition, with the option to specify a calculation level for aggregation.

Syntax

#Start#
distinctCountOver(
    field,               -- required, the field to count distinct values of
    [partition_field],   -- optional, one or more fields to partition by
    [calculation_level]   -- optional, the aggregation level for the calculation
)
#End#

This function takes the following arguments:

• field: the field for which the distinct count is calculated.

• partition_field (optional): one or more fields used to partition the data for the calculation. This parameter can be a field or an array of fields.

• calculation_level (optional): the aggregation level for the calculation. This parameter can be set to “cell”, “row”, “column”, or “grand_total”. By default, it is set to “cell”.

Here is an example of how the distinctCountOver function can be used in Amazon QuickSight:

Suppose we have a dataset containing information about employees in a company. We want to find out the distinct count of employees in each department. We can use the distinctCountOver function as follows:

Example

#Start#
distinctCountOver(employee_id, [department], "column")
#End#

In this example:

• employee_id is the field for which we want to count the distinct values.
• [department] is the partition field, indicating that we want to calculate the distinct count for each department.
• “column” is the calculation level, indicating that we want to display the distinct count as a column-level aggregation.

The result will be a table with a column for each department and the distinct count of employees in that department.