Reusing 3VL boolean dimension as a measure filter

  • 25 October 2016
  • 3 replies

I have a number dimension backed by a PostgreSQL boolean field (field < {number}). It works and returns 1, 0, <null>. I want to use this dimension in a measure filter:

type: count
field: TRUE

No bueno. The TRUE isn’t being applied, and in fact, will not parse. What’s going on?

3 replies

Userlevel 5

Hey @alyssackwan-clara, the way you’ll want to reference this field in a filter depends on the type of the dimension as you’ve defined it in Looker. If you’ve defined it as a number type, then field: 1 should do what you expect. If you’ve defined it as a yesno type, then you’d want field: 'yes' instead. Another alternative to number and yesno is a sql_case as in this example.

Let me know if these help, and feel free to visit for in-depth troubleshooting!

Using field: 1 got past the LookML compiler, but generates a nonsense SQL statement (of course). With my limited knowledge of Looker, I see three options:

  1. Keep the dimension as a number, and change the backing SQL expression to return 1/0/<null>.

  2. Change the dimension to yesno.

  3. Find some other way to apply the filter to the measure. (I really don’t know what this may be.)

Neither is ideal. FWIW, I have other dimensions using this dimension in their SQL arguments, and its fine. It’s the filter argument usage in this measure that’s the problem.

I’ll contact Support. Thanks!

Userlevel 5

For future reference, we ended up going with the CASE WHEN workaround described in this discourse article.