Dynamic Metrics Selection

knowledge checkpoint

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

• Parameter Fields
• AQL Expression
• Measure

Introduction

Using param fields, we can build charts with dropdown that allow users to change the metric dynamically.

Dynamic Metrics Selection

Assuming that you have a model with 3 metrics: total_orders, revenue, and total_users. You want to build a dashboard that let user pick which measure to be used in your report.

Model sales_model {

  measure total_orders { }
  measure revenue { }
  measure total_users { }

}

This can be implemented using Parameter Field and AQL Expression

Step 1: Create a Parameter Field

First, create a Parameter Field in the model.

Model sales_model {

  measure total_orders { }
  measure revenue { }
  measure total_users { }

  
  param metric_selector {
    label: 'Metric Selector'
    type: 'text'
    allowed_values: ['Total Orders', 'Revenue', 'Total Users']
  }

}

Step 2: Create a Dynamic Metric field

Create a Dynamic Metric that changes based on the selected parameter value.

Model sales_model {

  measure total_orders { }
  measure revenue { }
  measure total_users { }

  param metric_selector { }

  measure dynamic_metric {
    definition: @aql case(
      when: 'Total Orders' in sales_model.measure_selector
      , then: sales_model.total_orders

      , when: 'Revenue' in sales_model.measure_selector
      , then: sales_model.revenue

      , when: 'Total Users' in sales_model.measure_selector
      , then: sales_model.total_users
    ) ;;
  }

}

Step 3: Use Dynamic Metrics in the report

In the report, you can use Dynamic Metrics with another dimension.

Step 4: Use Parameter Field in the dashboard filter

In the dashboard filter, you can use Measure Selector Parameter Field to dynamically choose which measure to use in the report.

Advanced: Metrics selection at Dataset level

If the list of metrics are defined in Dataset instead of a single model:

1. Create a separate model to contain the param

Model param_model {
  type: 'query' // can be either `table` or `query` model

  query: @sql select 1 ;; // Dummy query

  param metric_selector {
    label: 'Metric Selector'
    type: 'text'
    allowed_values: ['Revenue', 'Total Orders', 'Total Users']
  }
}

2. Add the param_model to the Dataset and define the dynamic_metrics at the dataset level

Dataset ecommerce { 

  models: [countries, cities, users, orders, param_model]
  
  relationships: [ ]

  metric revenue { }
  metric total_orders { }
  metric total_users { }
  
  metric dynamic_metrics {
    label: 'Dynamic Metrics'
    type: 'number'
    definition: @aql
      case(
        when: 'Revenue' in param_model.metric_selector
        , then: revenue

        , when: 'Total Orders' in param_model.metric_selector
        , then: total_orders

        , when: 'Total Users' in param_model.metric_selector
        , then: total_users
      )
    ;;
  }
}