Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Reusing 3VL boolean dimension as a measure filter

  • 25 October 2016
  • 3 replies
  • 76 views
A

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

filters:

  field: TRUE

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

3 replies

sam
Rank
Userlevel 5
Badge

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 help.looker.com for in-depth troubleshooting!

A

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!

sam
Rank
Userlevel 5
Badge

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

Reply


Terms of Service