How do I filter out null in a filtered measure?

Looker Tips & Resources Technical Tips & Tricks
1

Knowledge Drop

Last tested: May 2020

Most of these examples can be found in the filter expressions doc.

Filter out NULL values for non-number dimensions

The most common filter syntax to filter out nulls is simply "-NULL" . This works for most data types that aren’t numbers, such as strings, dates, etc.

measure: count { type: count filters: { field: user.status value: "-NULL" # assuming that user.status is a string }}

Filter out NULL values for number dimensions

For type number, use NOT NULL instead.

measure: count { type: count filters: { field: user.age value: "NOT NULL"  # assuming that user.age is a number }}

Filter out the string “NULL” from a string dimension

What if your string dimension literally has the string “NULL” in it? (As opposed to a real null (Ø)) Then you’ll use this special syntax:

measure: count { type: count filters: { field: user.status value: "-'NULL'"  # assuming that user.status is a string that actually has 'NULL" as a value }}
2

Not working anymore

2 Likes
3

Still working fine. Go away Tableau people!

1 Like
4

Can you clarify how I would write an expression for custom filter to exclude null numbers?

When I use either ‘${field} NOT NULL’ or ‘${field} “NOT NULL”’, I get an error stating “expression incomplete”.