Dynamic Metric Conditions

knowledge checkpoint

A grasp of these concepts will help you understand this documentation better:

• Parameter Fields
• Where Function

Introduction

Dynamic metric conditions allow you to apply flexible filtering inside individual metric definitions using the where() function. This lets users control which data a specific metric includes, without affecting other metrics in the same explore.

When to use this vs. Dynamic Explore Conditions

• Dynamic Metric Condition (this page): Filters applied inside metric definitions using where() - affects only that specific metric
• Dynamic Explore Conditions: Filters applied in the visualization's filter/condition section - affects all metrics in the explore

Example: Filter Revenue by Merchant

Suppose you want to let users filter revenue by different merchants using a single metric, while keeping other metrics (like total orders) unaffected.

Pre-requisite: This example assumes you already have a merchants model with a name dimension and an orders model with a total_revenue measure.

merchants.model.aml

Model merchants {
  ...
  dimension name {
    label: "Merchant Name"
    type: 'text'
  }
}

orders.model.aml

Model orders {
  ...
  measure total_revenue {
    label: "Total Revenue"
    type: 'number'
  }
}

Step 1: Create a Parameter Model

Create a Query Model to hold your parameter field.

param_model.model.aml

Model param_model {
  type: 'query'
  label: 'Param Model'
  data_source_name: 'your_datasource'

  query: @sql select 1 ;;

  param merchant_param {
    label: 'Merchant'
    type: 'text'
  }
}

Step 2: Add Parameter Model to Your Dataset

Include the parameter model in your dataset and define metrics.

When your AQL expects a single value, use the first() function to extract only the first value:

ecommerce.dataset.aml

Dataset ecommerce {
  label: 'Ecommerce'
  data_source_name: 'your_datasource'

  models: [merchants, products, orders, order_items, param_model]

  relationships: [
    relationship(products.merchant_id > merchants.id, true),
    relationship(order_items.product_id > products.id, true),
    relationship(order_items.order_id > orders.id, true),
  ]

  // Regular metrics without parameter - NOT affected by dashboard filter in step 3
  metric total_revenue {
    label: 'Total Revenue'
    type: 'number'
    definition: @aql sum(orders.item_values) ;;
  }

  metric total_orders {
    label: 'Total Orders'
    type: 'number'
    definition: @aql count(orders.id) ;;
  }

  // Metric with parameter - ONLY this metric is affected by dashboard filter in step 3
  metric revenue_by_merchant {
    label: 'Revenue by Merchant'
    type: 'number'
    definition: @aql
      total_revenue
      | where(merchants.name == (param_model.merchant_param | first()))
    ;;
  }
}

When you use all three metrics in an explore and apply the dashboard filter, only revenue_by_merchant will be filtered - total_revenue and total_orders will still show the overall totals.

When your AQL can handle multiple values, use the in operator instead:

ecommerce.dataset.aml

Dataset ecommerce {
  label: 'Ecommerce'
  data_source_name: 'your_datasource'

  models: [merchants, products, orders, order_items, param_model]

  relationships: [
    relationship(products.merchant_id > merchants.id, true),
    relationship(order_items.product_id > products.id, true),
    relationship(order_items.order_id > orders.id, true),
  ]

  // Regular metrics without parameter - NOT affected by dashboard filter in step 3
  metric total_revenue {
    label: 'Total Revenue'
    type: 'number'
    definition: @aql sum(orders.item_values) ;;
  }

  metric total_orders {
    label: 'Total Orders'
    type: 'number'
    definition: @aql count(orders.id) ;;
  }

  // Metric with parameter - ONLY this metric is affected by dashboard filter in step 3
  metric revenue_merchants {
    label: 'Revenue (Multiple Merchants)'
    type: 'number'
    definition: @aql
      total_revenue
      | where(merchants.name in [param_model.merchant_param])
    ;;
  }
}

Step 3: Create Dashboard Filter

1. Add a dashboard filter linked to param_model.merchant_param
2. Users can now dynamically filter revenue by selecting one or more merchants

See Also

• Dynamic Explore Conditions - For filtering at the explore level
• Dynamic Query Model - For filtering at the SQL level
• Where Function Reference