This page explains how to filter and limit data in Looker. For example, you can filter your query results to the last three months or for a certain customer. You can also limit the number of rows displayed, or the number of pivot columns displayed.

Filtering data

Filters let you restrict the data that you are viewing to items of interest. Any field in your Looker instance can become a filter.

You also don’t necessarily need to add a dimension or measure to your results to filter on it. For example, you can create a query that filters the Order Date to the last 90 days, even though your results only show Customer and Number of Orders.

You can use any combination of these filter types in your query:

• Basic Filters are the most commonly used; Looker provides appropriate drop-down lists, calendar widgets, and text fields.

• Advanced Matches allow for a more advanced condition for a field, such as intricate text searches, or a date range that starts in the past and extends for a certain number of days.

• Custom Filters let you specify detailed business logic, combine AND and OR logic, or use Looker functions.

Basic filters

There are several ways to add a basic filter.

Filters appear in the Filters section above your results. To remove a filter, click the X to its right.

Standard filters

Standard filter options vary by filter type. For example, a time dimension lets you select a time range; a numeric dimension provides you with options such as equal to or greater than.

For text dimensions, Looker displays a list of existing data values for the field. As you type, Looker narrows the list to values that include that text.

When you type a filter value for a text dimension, Looker preserves leading or trailing spaces. For example, creating a filter that contains the text ” pants” would not match values that did not contain the leading space, such as “pants and leggings”. The filter would match any values that contained the space before the word pants, such as “parachute pants”.

If you copy and paste a value into a text filter, leading and trailing spaces are trimmed in the process.

To filter on a large set of values, paste a list of values into the filter field. The maximum number of values that you can paste may be limited by your browser or other elements in your network, and may be as small as 2,000.

To enter a special character in a standard filter, first add a leading backslash (\). For example, to filter on Santa Cruz, CA, you would enter Santa Cruz \, CA.

To add another option to the filter, click the + to the right of a filter. The new option appears as either an OR condition or an AND condition, depending on the type of filter option.

This example includes order counts over 5,000 or below 2,500, and excludes order counts of 1,500.

Filters with user attributes

Looker admins can configure user attributes that specify user-specific values. For example, an admin can define a user attribute for a sales region and assign the appropriate values to individual users or groups of users.

User attributes let you automatically customize a Look or dashboard for each user that views it. The Matches a user attribute provides this user-specific flexibility. For example, you can filter a sales region dimension in a Look to equal a sales region user attribute. The Look will filter for the user’s specific sales region and automatically adjust to show each user the data for their own sales region.

Advanced matches filters

There are several ways to add an advanced matches filter.

Filters appear in the Filters section above your results. To remove a filter, click the X to its right.

Select matches (advanced) from the filter’s drop-down menu:

In the text field, enter your filter expression. To view all available filter expressions, see the Looker filter expressions documentation page.

To enter a special character in an advanced matches filter, first add a leading carat (^). For example, to filter on Santa Cruz, CA, you would enter Santa Cruz ^, CA.

Your Looker admin can configure user-specific values called user attributes that let you automatically customize a Look for each user. To reference a user attribute in an advanced matches filter, use the syntax {{ _user_attributes['name_of_your_attribute'] }}.

Custom filters

Custom filters let you write the fields, constants, functions, and operators for your desired filtering. Looker lets you build an expression that evaluates as true or false. When you run the query, Looker only returns rows for which that condition is true.

Adding a custom filter

To add a custom filter, expand the Filters section.

Click the Custom Filter checkbox in the upper right:

Enter a dimension or function. Looker will display a list of functions, operators, and field names to use in your expression. Click on a term in the drop-down to add it to your expression. When finished, your expression must evaluate to true or false.

Click Run (or use the keyboard shortcut Command-Enter for Mac or Ctrl+Enter for Windows) to run your query with your custom filter applied.

The Creating Looker expressions documentation page explains how to create Looker expressions and how the editor can assist you.

Looker expressions can use as many fields, functions, and operators as your business logic requires. The more complex your condition, the more work your database must do to evaluate it; which may lengthen query times.

Because custom filters are used to create a database query, you cannot refer to measures in a custom filter, or use any functions that rely on query results. The Creating Looker expressions documentation page explains how to use Looker expressions in custom fields in more detail. The Looker functions and operators documentation page shows you the available functions and identifies which can be used in a custom filter.

Removing a custom filter

To remove a custom filter:

• Click the X to the right of the expression to delete it.
• Deselect the Custom Filter checkbox to stop using the expression. If you do not close the page, Looker remembers what you typed and your expression reappears if you click Custom Filter again.

Filtering dimensions: restricts raw data before calculations

When you filter on a dimension, you restrict the raw data before any calculations are made.

For example, you create this Explore to view how many orders were placed each day:

You add a filter on the Order Amount dimension:

Looker removes all orders that are less than $50 from the data. What remains is counted by the measure for each day.

Filtering measures: calculates before restricting results

When you filter on a measure, you restrict the results after the measure has been calculated

For example, you create this Explore to view how many orders were placed each day:

You add a filter on the Order Count measure:

Looker counts all orders for each day. Then, the filter is applied. The filter removes the days that had 350 or fewer orders, leaving the remaining days that had more than 350 orders.

Limiting data

Looker supports up to 5,000 rows and an unlimited number of columns for unpivoted queries. For browser performance, 50 or fewer columns is recommended. Looker supports up to 200 columns for pivoted queries, but sets a default column limit of 50 columns.

To see a subset of your complete query results, you can set a row limit, a column limit, or both.

Row limits

When you set a row limit, Looker only displays up to the number of rows you have set. Looker will warn you if you might be hiding data by setting a row limit that is too low. Your sort order is important; Looker first applies the sort, then applies the limit. For example, to see the top five states by number of orders sold, sort by orders.

If you reach a row limit, you cannot sort row totals or table calculations.

Column limits

If you added a pivot to your report, you can apply a column limit of up to 200. Looker warns you if you may hide data by setting a column limit that is too low. The sort order of your pivot is important; Looker first applies the sort, then applies the limit. For example, to see the five most recent months when orders were created, sort by the order created month.

Dimensions, dimension table calculations, row total columns, and measure table calculations outside of pivots are not counted toward the column limit. Pivoted groups count as one column toward the column limit.