| title | description | ms.reviewer | ms.topic | ms.date |
|---|---|---|---|---|
count_distinctif() (aggregation function) - (preview) |
Learn how to use the count_distinctif() function to count unique values of a scalar expression in records for which the predicate evaluates to true. |
alexans |
reference |
11/27/2022 |
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.
[!INCLUDE data-explorer-agg-function-summarize-note]
If you only need an estimation of unique values count, we recommend using the less resource-consuming dcountif aggregation function.
Note
- This function is limited to 100M unique values. An attempt to apply the function on an expression returning too many values will produce a runtime error (HRESULT: 0x80DA0012).
- Function performance can be degraded when operating on multiple data sources from different clusters.
count_distinctif (expr, predicate)
[!INCLUDE syntax-conventions-note]
| 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. |
Integer value indicating the number of unique values of expr per summary group, for all records for which the predicate evaluates to true.
This example shows how many types of death-causing storm events happened in each state. Only storm events with a nonzero count of deaths will be counted.
[!div class="nextstepaction"] Run the query
StormEvents
| summarize UniqueFatalEvents=count_distinctif(EventType,(DeathsDirect + DeathsIndirect)>0) by State
| where UniqueFatalEvents > 0
| top 5 by UniqueFatalEventsOutput
| State | UniqueFatalEvents |
|---|---|
| TEXAS | 12 |
| CALIFORNIA | 12 |
| OKLAHOMA | 10 |
| NEW YORK | 9 |
| KANSAS | 9 |