View the original community article here
Last tested: Apr 16, 2020
The Objective
To create a single filter value that impacts multiple values in order to be able to filter a measure on either dimension.
The Solution
We use a single parameter that our user can change the value with and we create yesno dimensions that listen to the input. If the dimension value equates to this value, we return a yes. Then, in a dimension we create a case when statement that evaluates these yesno dimensions and will tally a 1 if either case is satisfied. Finally if we want a total of how many dimensions fulfill either condition, we sum the final dimension. Code listed below.
parameter: param {
type: number
}
dimension: yesweight {
type: yesno
sql: {% parameter param %} = ${weight_high} ;;
}
dimension: yesheight {
type: yesno
sql: {% parameter param %} = ${height_high} ;;
}
dimension: liquid_dimension {
type: number
sql:
CASE
WHEN ${yesheight} IS TRUE THEN 1
WHEN ${yesweight} IS TRUE THEN 1
ELSE 0
END;;
}
measure: liquid_measure {
type: sum
sql: ${liquid_dimension} ;;
}
This content is subject to limited support.