Skip to main content

Measures

Measures are numerical values that mathematical functions can be applied to and are the foundation for creating metrics. An example of a measure is value of transactions - using this measure, you can create metrics like total transaction value or average transaction value. In Transform, measures are created in data sources and become the building blocks of your metrics.

Measures have a few inputs in the data source configuration:

Name (Required)

The name of the measure can be chosen by the user or optionally reference a database column directly. If the name of the measure is different than the column in the table, you must add an expr which denotes the column name. The name of the measure will be referenced when creating a metric. Measure names must be unique across all data sources in a project.

Description (Required)

The description section describes what measure is calculated. It is strongly recommended to create verbose and human-readable descriptions in this field.

Aggregation (Required)

The aggregation determines how the field will be aggregated. For example, a sum aggregation type over a granularity of day would sum the values across a given day.

Aggregation TypesDescription
sumSum across the values
minMinimum across values
maxMaximum across values
averageAverage across values
sum_booleanA sum for a boolean type
count_distinctDistinct count of values
tip

All of the metrics above, except for count_distinct, are additive measures. MetricFlow can aggregate these agg types sequentially to reuse datasets. As an example, transactions by day could be aggregated to transactions by week. Non-additive measures like count_distinct don't have this property and must be calculated from the source every time. This can be more costly and slower, but the resulting metrics are worth the wait in many cases.

Expr (Optional)

Use an expr with any valid SQL to manipulate the output of the value of the measure. This could be a SQL statement or it could simply be the name of the column in the database. Using an expr is required if you've specified a name that does not match the name of the measure column in the database. In this case, the expr column should reflect the column name.

caution

If you are using Snowflake and use any week-level function inside the expr parameter, the function will now return the ISO-standard default of MONDAY.

Examples:

  1. You are running Snowflake warehouse instances with account or session level overrides for the WEEK_START parameter that fix it to a value other than 0 or 1: You will now see MONDAY as the week start in all cases.

  2. You are using the DAYOFWEEK function inside the expr parameter with the legacy Snowflake default of WEEK_START = 0: DAYOFWEEK will now return the ISO-standard values of 1 (Monday) through 7 (Sunday) instead of Snowflake’s legacy default values of 0 (Monday) through 6 (Sunday).

Create Metric

You can optionally choose to create a metric directly from a measure. This is a shortcut for creating a metric. By default, the description and owners will default to the description and owner of the data source. The tier of the metric will default to Tier 3.

You can currently specify the create metric and what the display name of the metric is:

create_metric: true create_metric_display_name: "Your Metric Display Name"

Note: If you prefer to have a single file location or a single mechanism for storing all your metric definitions, we do not recommend you use this shortcut because this allows metric definitions to exist in multiple places across your model.

Examples

Using various Aggregation Types

measures:
- name: transaction_amount_usd
description: The total USD value of transactions daily
agg: sum
measures:
- name: transaction_amount_usd_avg
description: The average USD value of the transaction.
expr: transaction_amount_usd
agg: average
measures:
- name: transaction_amount_usd_max
description: The maximum USD value of the transaction.
expr: transaction_amount_usd
agg: max
measures:
- name: transaction_amount_usd_min
description: The minimum USD value of the transaction.
expr: transaction_amount_usd
agg: min
measures:
- name: quick_buy_transactions
description: The total transactions bought as quick buy.
expr: quick_buy_flag
agg: sum_boolean
measures:
- name: distinct_transactions_count
description: Distinct count of transactions
expr: transaction_id
agg: count_distinct

Using expr to compute the aggregation based on a condition. This will sum the values for when is_valid = true.

measures:
- name: transactions_amount_usd
description: The sum USD value of valid transactions
agg: sum
expr: CASE WHEN is_valid = true THEN 1 ELSE 0;

Using Create Metric to create a metric from the measure

measures:
- name: transactions
description: The average value of transactions.
expr: transaction_amount_usd
agg: average
create_metric: true
create_metric_display_name: Avg Value of Transactions (Daily)