Amazon QuickSight – distinct_countIf
The ‘distinct_countIf’ function in Amazon QuickSight is used to count the number of distinct values in a given dimension or measure that meet a specified condition. Here is an explanation of the distinct_countIf() function:
Syntax
#Start# distinct_countIf(dimension or measure, condition) #End#
This function takes two arguments:
- dimension or measure: This parameter specifies the dimension or measure for which the distinct count is to be computed.
- condition: This parameter specifies the condition that must be met for the value to be counted. The condition can be a logical expression, such as “Sales > 1000” or “Category = ‘Electronics'”.
Suppose we have a dataset that contains information about sales transactions and we want to count the number of distinct customers who have made a purchase of more than $1000. We can use the distinct_countIf() function in Amazon QuickSight to do this.
To count the number of distinct customers who have made a purchase of more than $1000, we can create a new calculated field in Amazon QuickSight with the following expression:
Example
#Start# distinct_countIf(CustomerID, Sales > 1000) #End#
This expression will count the number of distinct customers who have made a purchase of more than $1000.
For instance, if our sales transaction data is as follows:
| Transaction ID | Customer ID | Sales Amount |
| 1 | 123 | $1500 |
| 2 | 456 | $500 |
| 3 | 789 | $2000 |
| 4 | 123 | $1200 |
| 5 | 456 | $3000 |
| 6 | 789 | $900 |
Then, the distinct count of customers who have made a purchase of more than $1000 will be:
Example
#Start# distinct_countIf(CustomerID, Sales > 1000) = 2 #End#
In this example, we can see that the distinct_countIf() function has counted the number of distinct customers who have made a purchase of more than $1000, resulting in a useful metric that can inform our business decisions.