make_set() (aggregation function)

  • Article

Applies to: ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

Creates a dynamic array of the set of distinct values that expr takes in the group.

Null values are ignored and don't factor into the calculation.

Note

This function is used in conjunction with the summarize operator.

Deprecated aliases: makeset()

Syntax

make_set(expr [, maxSize])

Learn more about syntax conventions.

Parameters

Name Type Required Description
expr string ✔️ The expression used for the aggregation calculation.
maxSize int The maximum number of elements returned. The default and max value is 1048576.

Note

The deprecated version has a default maxSize limit of 128.

Returns

Returns a dynamic array of the set of distinct values that expr takes in the group. The array's sort order is undefined.

Tip

To only count distinct values, use dcount() or count_distinct().

Example

Set from a scalar column

The following example shows the set of states grouped with the same amount of crop damage.

StormEvents 
| summarize states=make_set(State) by DamageCrops

The results table shown includes only the first 10 rows.

DamageCrops states
0 ["NORTH CAROLINA","WISCONSIN","NEW YORK","ALASKA","DELAWARE","OKLAHOMA","INDIANA","ILLINOIS","MINNESOTA","SOUTH DAKOTA","TEXAS","UTAH","COLORADO","VERMONT","NEW JERSEY","VIRGINIA","CALIFORNIA","PENNSYLVANIA","MONTANA","WASHINGTON","OREGON","HAWAII","IDAHO","PUERTO RICO","MICHIGAN","FLORIDA","WYOMING","GULF OF MEXICO","NEVADA","LOUISIANA","TENNESSEE","KENTUCKY","MISSISSIPPI","ALABAMA","GEORGIA","SOUTH CAROLINA","OHIO","NEW MEXICO","ATLANTIC SOUTH","NEW HAMPSHIRE","ATLANTIC NORTH","NORTH DAKOTA","IOWA","NEBRASKA","WEST VIRGINIA","MARYLAND","KANSAS","MISSOURI","ARKANSAS","ARIZONA","MASSACHUSETTS","MAINE","CONNECTICUT","GUAM","HAWAII WATERS","AMERICAN SAMOA","LAKE HURON","DISTRICT OF COLUMBIA","RHODE ISLAND","LAKE MICHIGAN","LAKE SUPERIOR","LAKE ST CLAIR","LAKE ERIE","LAKE ONTARIO","E PACIFIC","GULF OF ALASKA"]
30000 ["TEXAS","NEBRASKA","IOWA","MINNESOTA","WISCONSIN"]
4000000 ["CALIFORNIA","KENTUCKY","NORTH DAKOTA","WISCONSIN","VIRGINIA"]
3000000 ["CALIFORNIA","ILLINOIS","MISSOURI","SOUTH CAROLINA","NORTH CAROLINA","MISSISSIPPI","NORTH DAKOTA","OHIO"]
14000000 ["CALIFORNIA","NORTH DAKOTA"]
400000 ["CALIFORNIA","MISSOURI","MISSISSIPPI","NEBRASKA","WISCONSIN","NORTH DAKOTA"]
50000 ["CALIFORNIA","GEORGIA","NEBRASKA","TEXAS","WEST VIRGINIA","KANSAS","MISSOURI","MISSISSIPPI","NEW MEXICO","IOWA","NORTH DAKOTA","OHIO","WISCONSIN","ILLINOIS","MINNESOTA","KENTUCKY"]
18000 ["WASHINGTON","WISCONSIN"]
107900000 ["CALIFORNIA"]
28900000 ["CALIFORNIA"]

Set from array column

The following example shows the set of elements in an array.

datatable (Val: int, Arr1: dynamic)
[
    1, dynamic(['A1', 'A2', 'A3']), 
    5, dynamic(['A2', 'C1']),
    7, dynamic(['C2', 'A3']),
    5, dynamic(['C2', 'A1'])
] 
| summarize Val_set=make_set(Val), Arr1_set=make_set(Arr1)
Val_set Arr1_set
[1,5,7] ["A1","A2","A3","C1","C2"]

Related content

  • Use mv-expand operator for the opposite function.
  • make_set_if operator is similar to make_set, except it also accepts a predicate.