Assuming that you have a model model_with_2_dims with 2 dimensions: dim1 and dim2. You want your users to be able choose an option from a few selected values, then dynamically select dim1 or dim2 based on that input during runtime.
Model model_with_2_dims {
dimension dim1 { ... }
dimension dim2 { ... }
}
You can do that with the code below:
Model model_with_dynamic_dim {
type: 'query'
label: 'Model with dynamic dim'
description: ''
data_source_name: 'my_ds'
owner: 'my_email'
param dimension_choice {
label: 'Selected Dimension'
type: 'text'
allowed_values: ["choice1", "choice2"]
}
dimension dynamic_dimension {
label: 'Dynamic Dimension'
type: 'text'
hidden: false
definition: @sql {{ #SOURCE.dynamic_dimension }};;
}
query: @sql
select CASE
WHEN {% filter(dimension_choice) %} 'choice1' {% end %} THEN dim1
WHEN {% filter(dimension_choice) %} 'choice2' {% end %} THEN dim2
ELSE 'unknown'
END as dynamic_dimension
from {{ #model_with_2_dims }}
;;
}
The full documentation for this can be found here.
Cheers!