toscalar()

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

Returns a scalar constant value of the evaluated expression.

This function is useful for queries that require staged calculations. For example, calculate a total count of events, and then use the result to filter groups that exceed a certain percent of all events.

Any two statements are separated by a semicolon.

Syntax

toscalar(expression)

Learn more about syntax conventions.

Parameters

Returns

A scalar constant value of the evaluated expression. If the result is a tabular, then the first column and first row is taken for conversion.

Limitations

toscalar() can't be applied on a scenario that applies the function on each row. This is because the function can only be calculated a constant number of times during the query execution. Usually, when this limitation is hit, the following error is returned: can't use '<column name>' as it is defined outside its row-context scope.

In the following example, the query fails with the error:

'toscalar': can't use 'x' as it is defined outside its row-context scope.

let _dataset1 = datatable(x:long)[1,2,3,4,5];
let _dataset2 = datatable(x:long, y:long) [ 1, 2, 3, 4, 5, 6];
let tg = (x_: long)
{
    toscalar(_dataset2| where x == x_ | project y);
};
_dataset1
| extend y = tg(x)

This failure can be mitigated by using the join operator, as in the following example:

let _dataset1 = datatable(x: long)[1, 2, 3, 4, 5];
let _dataset2 = datatable(x: long, y: long) [1, 2, 3, 4, 5, 6];
_dataset1
| join (_dataset2) on x 
| project x, y

Output

Additional mitigation patterns for real-world scenarios

In many practical scenarios, you may want to compute a scalar value per row using an expression that performs its own aggregation, such as:

| extend result = toscalar(T | where Key == key | summarize max(Value))

This pattern fails because toscalar() cannot be evaluated once per row. Use one of the supported mitigation patterns below.

1. Pre-aggregate the data once and then join the aggregated results back to the main table for improved efficiency.

let summary =
    T
    | summarize maxValue = max(Value) by Key;

Dataset1
| join kind=leftouter summary on Key
| project Key, maxValue

2. Use arg_max() to retrieve the row with the highest value. This is useful when you need both the maximum value and the associated columns.

let summary =
    T
    | summarize arg_max(Timestamp, *) by Key;

Dataset1
| lookup summary on Key

3. Use a lookup for key/value mappings to avoid row-context violations and ensure efficient dimension-table lookups.

let lookupTable =
    T | summarize maxValue = max(Value) by Key;

Dataset1
| lookup lookupTable on Key

4. Use window functions or make-series for time-window aggregations

Dataset1
| make-series maxValue = max(Value)
      on Timestamp
      from ago(1h) to now()
      step 1m
      by Key

Examples

The examples in this section show how to use the syntax to help you get started.

Set range for evaluation

Evaluate Start, End, and Step as scalar constants, and use the result for range evaluation.

let Start = toscalar(print x=1);
let End = toscalar(range x from 1 to 9 step 1 | count);
let Step = toscalar(2);
range z from Start to End step Step | extend start=Start, end=End, step=Step

Output

Generate fixed and dynamic GUID

The following example shows how toscalar() can be used to generate a fixed guid, calculated precisely once, and dynamic values for guid.

let g1 = toscalar(new_guid());
let g2 = new_guid();
range x from 1 to 2 step 1
| extend x=g1, y=g2

Output

Related content

• Scalar function types at a glance