Filtered Measure that listens to two different dimensions with a single filter input.

[LookerExperts]

Knowledge Drop

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.