Parametrised values not working as expected

Looker Q&A Looker
1

Hi,

I’m trying to use parameters to switch between different columns in a visualisation. So the user can select measure a, b or c as desired.

I’ve created the parameters as required below (with aliases used for field labels/values):

  parameter: new_parameter {
    type: unquoted
    allowed_value: {label: "a label"
      value: "a"}
    allowed_value: {label: "b label"
      value: "b"}
    allowed_value: {label: "c label"
      value: "c"}
    default_value: "a"
  }

Then the associated measure:

  measure: new measure {
    type: sum
    sql: ${TABLE}.{% parameter new_parameter %} ;;
  }

But when I try and run these I get a message to the affect of “Name a not found inside of Table”. This is in spite of the measures used working fine in other contexts. Any ideas why this may be happening? Help is greatly appreciated!

1 Like
2

It means that your table doesn’t have a column named “a”

1 Like
3

Thanks for the reply @Dawid . To clarify, “a”, “b”, “c” are measures created in the same view where I’m creating the parameter. I’ve successfully used them in other explores/visuals, it is only in the parameter context they don’t work. Does this mean measures created in this way can’t be used in parameters?

1 Like
4

But by using the ${TABLE} you’re referring directly to the database not your view. This

${{% parameter new_parameter %}}

won’t work.. I tried many times :smiley:

1 Like
5

OK, thanks for explaining @Dawid . Clearly confused myself reading the official docs. Last question; is there a way round this without the measure being in the table as opposed to just the view?

1 Like
6

Yes, there’s another way of doing it but a bit more code, unfortunately..

measure: new_measure {
  sql:
    {% if new_parameter._parameter_value == "a" %}
      ${a}
    {% elsif new_parameter._parameter_value == "b" %}
      ${b} 
    {% endif %} ;;
}

You’re losing the dynamic part because you can’t inject the name of the parameter, which means every time you add a new allowed_value, you have to remember to add another elsif..

2 Likes