A measure’s context is its grouping and its filters. EVAL works out a measure in a context that you set, for one value. The rows the query returns stay the same. So one query can show each region’s orders beside the orders of the whole shop, the region’s web orders or the month before.

How it works

Each EVAL has its own context, and it changes only its own value. A modifier inside the EVAL says how its context differs from the row’s context. This query shows Orders by region, and beside it one EVAL of Orders for each modifier:

1
SELECT
2
  shop.region,
3
  shop.count_distinct__order_id,
4
  MEASURE('all_orders', EVAL(shop.count_distinct__order_id, CLEAR())) AS "CLEAR",
5
  MEASURE('without_region', EVAL(shop.count_distinct__order_id, REMOVE(shop.region))) AS "REMOVE",
6
  MEASURE('web_orders', EVAL(shop.count_distinct__order_id, SET(shop.channel = 'web'))) AS "SET",
7
  MEASURE('all_web_orders', EVAL(shop.count_distinct__order_id, SET_CUSTOM(shop.channel = 'web'))) AS "SET_CUSTOM"
8
FROM workspace.shop AS shop
9
GROUP BY
10
  shop.region
11
ORDER BY
12
  shop.region

The query still has one row per region. Only the numbers in the EVAL columns change. Each one has its own name from MEASURE. Two EVALs of Orders in one SELECT would both take the name of Orders.

The forms

SET and SET_CUSTOM differ in what they keep. SET(shop.channel = 'web') keeps the region of the row, so EMEA reads its own 185 web orders. SET_CUSTOM(shop.channel = 'web') replaces the whole context, so every row reads the shop’s 706 web orders.

Examples

Share of all orders

What share of all orders does each region have?

1
SELECT
2
  shop.region,
3
  shop.count_distinct__order_id,
4
  MEASURE('all_orders', EVAL(shop.count_distinct__order_id, CLEAR())) AS "All Orders",
5
  MEASURE(
6
    'share_of_all_orders',
7
    EVAL(shop.count_distinct__order_id) / NULLIF(EVAL(shop.count_distinct__order_id, CLEAR()), 0)
8
  ) AS "Share of All Orders" /* @format percent(1) */
9
FROM workspace.shop AS shop
10
GROUP BY
11
  shop.region
12
ORDER BY
13
  shop.region

The first EVAL follows the row’s region. The CLEAR() one is always the grand total of 3,000. The share is a measure, so a reader that groups it another way gets each group’s share of all orders.

Share within each region

What share of each region’s orders comes from each channel?

1
SELECT
2
  shop.region,
3
  shop.channel,
4
  shop.count_distinct__order_id,
5
  MEASURE('region_orders', EVAL(shop.count_distinct__order_id, REMOVE(shop.channel))) AS "Region Orders",
6
  MEASURE(
7
    'share_of_region_orders',
8
    EVAL(shop.count_distinct__order_id) / NULLIF(EVAL(shop.count_distinct__order_id, REMOVE(shop.channel)), 0)
9
  ) AS "Share of Region Orders" /* @format percent(1) */
10
FROM workspace.shop AS shop
11
GROUP BY
12
  shop.region,
13
  shop.channel
14
ORDER BY
15
  shop.region,
16
  shop.channel

The first 8 of 20 rows. REMOVE(shop.channel) takes the channel out of the grouping and keeps the region, so each row divides by its own region’s orders.

One member beside every row

How do the web orders of EMEA compare with each of its channels?

1
SELECT
2
  shop.region,
3
  shop.channel,
4
  shop.count_distinct__order_id,
5
  MEASURE('web_orders', EVAL(shop.count_distinct__order_id, SET(shop.channel = 'web'))) AS "Web Orders"
6
FROM workspace.shop AS shop
7
WHERE
8
  shop.region = 'EMEA'
9
GROUP BY
10
  shop.region,
11
  shop.channel
12
ORDER BY
13
  shop.channel

SET replaces the channel of each row with web, and keeps the region and the filter on it. So every row reads EMEA’s 185 web orders.

A condition of your own

How many orders do EMEA and LATAM have together, beside each region?

1
SELECT
2
  shop.region,
3
  shop.count_distinct__order_id,
4
  MEASURE(
5
    'emea_latam_orders',
6
    EVAL(shop.count_distinct__order_id, SET_CUSTOM(shop.region IN ('EMEA', 'LATAM')))
7
  ) AS "EMEA and LATAM Orders"
8
FROM workspace.shop AS shop
9
GROUP BY
10
  shop.region
11
ORDER BY
12
  shop.region

SET takes one value, and SET_CUSTOM takes any condition on the measure’s dimensions. The condition replaces all of the grouping and all of the query’s filters, so every row reads 777 plus 760.

The previous month

How many orders did each month have, and how many did the month before have?

1
SELECT
2
  shop.order_month,
3
  shop.count_distinct__order_id,
4
  DIMENSION(
5
    'orders_previous_month',
6
    EVAL(
7
      shop.count_distinct__order_id,
8
      SET(shop.order_month = CURRENT(shop.order_month) - INTERVAL 1 MONTH)
9
    )
10
  ) AS "Orders in the Previous Month"
11
FROM workspace.shop AS shop
12
WHERE
13
  shop.order_month >= DATE '2026-06-01'
14
GROUP BY
15
  shop.order_month
16
ORDER BY
17
  shop.order_month

CURRENT(shop.order_month) is the month of the row. The SET replaces the order month’s grouping and its filter. So June still finds May’s 155 orders, although the WHERE keeps June to September.

• The shifted value needs its own name. Orders and the EVAL of Orders would both take the name count_distinct__order_id. The compiler refuses that query with duplicate_output_field. The DIMENSION declaration names the shifted value. Readers get it as a value fixed at this query’s grouping.
• A number counts periods of the dimension. An order month is a date: the first day of the month. CURRENT(shop.order_month) - 1 is the month before, the same as - INTERVAL 1 MONTH. On a year, such as KATER__YEAR(shop.order_date), - 1 is the year before. On a date with no grain, - 1 is the day before.
• CURRENT needs the dimension in the context. The query must group by the dimension, or filter it to one value.
• CURRENT works in an EVAL and inside a MEASURE. A reader of the measure cannot yet use a SET on a dimension that the same SELECT declares with DIMENSION. For a comparison that readers can work out again, see Period comparisons.

Reader filters and CLEAR()

What does each modifier do with the query’s own filter?

1
SELECT
2
  shop.region,
3
  shop.count_distinct__order_id,
4
  MEASURE('cleared_orders', EVAL(shop.count_distinct__order_id, CLEAR())) AS "Orders with CLEAR",
5
  MEASURE('removed_orders', EVAL(shop.count_distinct__order_id, REMOVE(shop.region))) AS "Orders with REMOVE"
6
FROM workspace.shop AS shop
7
WHERE
8
  shop.channel = 'web'
9
GROUP BY
10
  shop.region
11
ORDER BY
12
  shop.region

CLEAR() drops the grouping and the query’s filter, so it counts all 3,000 orders. REMOVE(shop.region) drops only the region, so it keeps the filter and counts the 706 web orders. For a share of the filtered total, use REMOVE on the grouping. REMOVE also drops the filter on a dimension it names: here, REMOVE(shop.channel) would read each region’s orders in every channel.

The definition still applies

Suppose the orders Kater dataset keeps only the orders that have a region:

1
FROM shop.sales.orders AS orders
2
WHERE
3
  REQUIRED_FILTER('is_not_null__region', KATER__IS_NOT_NULL(orders.region), label='Region is set')

A query of Orders by channel, with the grand total beside it:

1
SELECT
2
  shop.channel,
3
  shop.count_distinct__order_id,
4
  MEASURE('cleared_orders', EVAL(shop.count_distinct__order_id, CLEAR())) AS "Orders with CLEAR"
5
FROM workspace.shop AS shop
6
GROUP BY
7
  shop.channel
8
ORDER BY
9
  shop.channel

CLEAR() drops the query’s grouping and filters, but not the measure’s definition. The WHERE and the required filters of the model that declares the measure, and its joins, stay in force under every modifier. So do the required filters of each model that passes the measure on, such as a semantic model’s. So the grand total is 2,936, the orders that have a region.

Calculate at

In a query, the field menu of a measure that the query reads from its source has Calculate at. It offers Ignore Grouping By, which works the measure out without some of the query’s grouping. Its label names the dimension it ignores, such as “Orders (Across Channel)”.

1
SELECT
2
  shop.region,
3
  shop.channel,
4
  shop.count_distinct__order_id,
5
  MEASURE(
6
    'across_channel__count_distinct__order_id',
7
    KATER__IGNORE_GROUPING_BY(shop.count_distinct__order_id, shop.channel)
8
  ) AS "Orders (Across Channel)"
9
FROM workspace.shop AS shop
10
WHERE
11
  shop.region = 'EMEA'
12
GROUP BY
13
  shop.region,
14
  shop.channel
15
ORDER BY
16
  shop.channel

The compiler turns KATER__IGNORE_GROUPING_BY(m, dim, ...) into EVAL(m, REMOVE(dim, ...)), so each row reads EMEA’s 777 orders in every channel. The menu declares it as a MEASURE, so a query that reads this one works it out again at its own grouping.

Rules

• One kind of modifier in each EVAL. Use one CLEAR(), one REMOVE, one SET_CUSTOM, or one or more SETs. Several SETs apply in order, and each one sees the context the one before it set: SET(shop.region = 'EMEA'), SET(shop.channel = 'web') reads 185 on every row. Any other mix, such as REMOVE with SET, or SET with CLEAR(), is refused.
• CLEAR() also drops the query’s filters. To keep the filters, remove only the grouping with REMOVE.
• SET replaces the filter on its own dimension. The other filters stay.
• SET_CUSTOM replaces everything. Its condition names at least one of the measure’s dimensions. It cannot read a measure, an aggregate, a window or a parameter.
• The definition still applies. The WHERE of the model that declares the measure, its required filters and its joins stay in force under every modifier. So do the required filters of each model that passes the measure on.
• A modifier cannot break the measure’s rules. EVAL(shop.sum__amount, CLEAR()) would add euros to dollars. The compiler refuses it: “Revenue cannot be added up across Currency, so CLEAR() cannot drop the grouping. SET Currency to one value instead.” See Measures you cannot add up.
• A dimension takes no modifiers. An EVAL of a dimension is the same dimension, with the same name.
• An alias does not rename an EVAL. EVAL(m) and EVAL(m, CLEAR()) AS all_orders in one SELECT are both named after m. Declare the second with its own name, such as MEASURE('all_orders', EVAL(m, CLEAR())).

If you know another tool

Common errors