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
         )
       ;;
     }
   }