count_distinctif() (aggregation function) - (preview)
Conditionally counts unique values specified by the scalar expression per summary group, or the total number of unique values if the summary group is omitted. Only records for which predicate evaluates to true are counted.
If you only need an estimation of unique values count, we recommend using the less resource-consuming dcountif aggregation function.
Syntax
count_distinctif (expr, predicate)
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
| expr | scalar | ✔️ | The expression whose unique values are to be counted. |
| predicate | string | ✔️ | The expression used to filter records to be aggregated. |
Returns
Integer value indicating the number of unique values of expr per summary group, for all records for which the predicate evaluates to true.
Example
The following example shows how many types of death-causing storm events happened in each state. Only storm events with a nonzero count of deaths are counted.
StormEvents
| summarize UniqueFatalEvents=count_distinctif(EventType,(DeathsDirect + DeathsIndirect)>0) by State
| where UniqueFatalEvents > 0
| top 5 by UniqueFatalEvents
Output
| State | UniqueFatalEvents |
|---|---|
| TEXAS | 12 |
| CALIFORNIA | 12 |
| OKLAHOMA | 10 |
| NEW YORK | 9 |
| KANSAS | 9 |
Related content
- Aggregation function types at a glance
- count_distinct() (aggregation function)
- countif() (aggregation function)
- dcountif() (aggregation function)
Feedback
Was this page helpful?
Glad to hear it! Please tell us how we can improve.
Sorry to hear that. Please tell us how we can improve.