Skip to content
M↓ View as Markdown ↗

Measures

Measures are the numbers you calculate from your data, such as the answer to "How many customers?" or "What's total revenue?" Unlike dimensions, which you filter or group by, a measure always uses an aggregation function. You don't display it row by row; you calculate it across a set of records.

Common aggregation functions:

  • COUNT() - Count records
  • COUNT(DISTINCT) - Count unique values
  • SUM() - Add up values
  • AVG() - Calculate average
  • MIN() / MAX() - Find minimum or maximum

Add or edit a measure

Availability

  • Who can do this: members with the Edit capability in the active Workspace.
  1. In Studio, open Models and select a model.
  2. Open the Measures tab.
  3. Select a measure in the list to edit it, or click New measure at the bottom of the list.
  4. Fill in the fields described below.
  5. Click Save measure.

To delete a measure, select it and click the Remove (trash) icon next to its name, then confirm Remove measure?.

Each measure name must be unique within its model. If you reuse a name, you see "This model already has a measure with that name".

Measure fields

Measure name

A clear identifier that describes what this measure calculates.

Examples: total_customers, total_revenue, average_order_value

Description

Explain what this measure calculates and when to use it.

Example: "Total count of unique customers" or "Average order value across all transactions"

Unit

Type the unit label shown after the value (for example USD, %, customers). The unit is display-only and does not change the calculation.

Common units:

  • Counts: customers, orders, items, products, users, transactions
  • Currency: EUR, USD, GBP, JPY
  • Percentages: %, rate, ratio
  • Time: seconds, minutes, hours, days
  • Other: bytes, KB, MB, GB

Example: If you're counting customers, enter customers as the unit. The result might display as "1,247 customers".

Precision

The number of decimal places for the value, from 0 to 20. Leave it empty for no rounding.

  • 0 for whole numbers (e.g., counts of users: "127")
  • 2 for currency or percentages (e.g., "1,234.56" or "45.32%")
  • Higher values for precise scientific calculations

SQL expression

The SQL aggregation that produces the number, for example SUM(amount). This is where you specify the aggregation function.

Examples:

  • COUNT(DISTINCT(id)) - Count unique IDs
  • COUNT(*) - Count all records
  • SUM(revenue) - Sum up revenue values
  • AVG(duration) - Average duration

Keep in mind:

  • The expression must contain an aggregation. If it doesn't, AI Analyst won't save it and reports "Measure expression must contain an aggregation (e.g. SUM, COUNT, AVG)".
  • The expression is stored and run as written, in the SQL dialect of the model's data connection.
  • The editor validates the expression against the warehouse as you type.

Sample value

As you edit the SQL expression, AI Analyst calculates the measure against your data and shows the result below the editor, with the unit after it ("Loading value…" while it runs). If the warehouse or SemQL compiler rejects the expression, its error message appears under the editor and Save measure stays disabled until the expression validates. If the check can't be completed, you see "Failed to load value".

Example: "1,247 customers" indicates your measure is counting successfully.

Measures across joins (fan-out)

When a query joins models, a join can repeat a model's rows. For example, if you join customers to orders, each customer row appears once per order. Summing or counting a customers measure over those repeated rows would give an inflated number.

AI Analyst guards against this. When a query joins models on a relationship's keys and that relationship is to-many as seen from the measure's model, it refuses SUM, COUNT and AVG on that model's measures, whether written directly or as a saved measure:

Aggregation Behavior
SUM, COUNT, AVG Refused, instead of returning an inflated result
COUNT(DISTINCT ...), MIN, MAX Allowed, because repeated rows don't change the result

AI Analyst decides where rows repeat from the relationship Type you declared, so make sure each relationship has the correct type. The check applies only to joins made on the relationship's From key and To key, so both keys must be dimensions on their models. Joins on other columns are not checked. See Relationships.

Limiting which measures an AI Analyst can use

By default, an AI Analyst that can access a model can use all of its measures. When an AI Analyst's model access is set to Choose specific models, you can narrow which dimensions and measures of each model it may use. See Models in the AI Analyst configuration.

Building blocks for metrics

A measure performs a single aggregation. A metric can combine multiple measures with business logic.

Learn more about Metrics →