home User Guide Getting Started Help Center Documentation Community Training Certification
menu
close
settings
Looker keyboard_arrow_down
language keyboard_arrow_down
English
Français
Deutsch
日本語
search
print
Filtering and Limiting Data

Looker lets you limit the data you see based on criteria you specify. For example, you might want to filter the 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. This page introduces you to filtering and limiting data in Looker.

Filtering Data Overview

You can restrict the data you’re viewing to items of interest by adding filters. For example, you might limit the results to certain dates, customers, locations, or anything else that is part of your data. Any field in your Looker instance can become a filter.

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

There are several different types of filters:

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

Basic Filters

There are several different ways to add a basic filter:

You’ll see that a filter appears in the Filters section above your results. To remove a filter, click the X to its right.

Standard Types of Filter Options

The available options for the filter depend on its type.

For example, a time dimension will have options to select a time range, while a numeric dimension will have options like equal to or greater than.

For text dimensions, Looker helps you choose filter values by displaying a list of existing data values for that field. As you type, Looker narrows the list to just those values that include that text.

When you type a filter value for a text dimension, Looker preserves any 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”. However, 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, however, leading and trailing spaces are trimmed as part of the copy and paste process.

If you need to enter a special character in your filter, such as , or ", you can do so by adding a leading backslash (\). For example, to filter on Santa Cruz, CA, you would enter Santa Cruz \, CA.

Entering multiple values into a filter, where appropriate, creates an OR condition, meaning all of the values are applied to the filter.

If you would like to filter on a large set of values, you can paste a long list of values into the filter field. However, the maximum number of values you can paste may be limited by your browser or other elements along your network, and may be as small as 2,000.

Click the + to the right of a filter to add another option to the filter. The new option will appear as either an OR condition or an AND condition, depending on the type of filter option. For example:

This example includes all created dates with an order count over 100 or below 50, and excludes all days with order counts of 41, 8, or 224.

If you need a more complex filter or set of filters, you can create a custom filter.

Adapt Filters to Each User with User Attributes

One of the basic filter options, matches a user attribute, provides user-specific flexibility:

Advanced Matches Filters

Adding an advanced matches filter starts the same way as a basic filter:

Next, select matches (advanced) from the drop-down menu of your filter:

This will give you a text field to type in a filter expression. To remove the filter, click the X to its right. Clicking the help link takes you to a page listing the various filter expressions you can use.

In advanced matches filters, unlike regular filters, if you need to enter a special character in your filter, such as , or ", you can do so by added a leading carat (^). For example, to filter on Santa Cruz, CA, you would enter Santa Cruz ^, CA.

As mentioned above, your Looker admin can configure user-specific values called user attributes that let you automatically customize a Look for each user that views it. If you want 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 create filters with custom conditions that might not be available with the other, simpler filter types. These conditions can be simple or complex. You write the fields, constants, functions, and operators that express the filtering that you want.

When you add a custom filter, Looker displays an editor for you to build an expression that evaluates as true or false. When you run the query, Looker will only return rows for which that condition is true.

Adding a Custom Filter

To add a custom filter, expand the Filters section and click the Custom Filter checkbox in the upper right:

To create a Looker expression for your custom filter, start typing a dimension or function. Looker will display a list of functions, operators, and field names that you might want 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 page explains how to create Looker expressions and how the editor helps you.

Looker expressions can use as many fields, functions, and operators as your business logic requires. Just keep in mind that 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 page explains how to use Looker expressions in custom fields in more detail. The Looker Functions and Operators 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, you can:

The Difference Between Filtering Dimensions and Filtering Measures

Filters are applied differently to dimensions and measures.

Filtering Dimensions: Restricts Raw Data Before Calculations

When you filter on a dimension, you are restricting the raw data before any calculations are made. For example, if you want to see how many orders were placed each day, you could create an Explore that looks like this:

Then you decide you only want to count orders over $50, so you add a filter on the Order Amount dimension:

You will still see results for each day, but the order counts are smaller. All of the orders that are less than $50 are removed from the data, and what remains is counted by the measure for each day.

Filtering Measures: Calculates First, Then Restricts the Results

When you filter on a measure, however, you are restricting the results after the measure has been calculated. For example, start from the same Explore used in the prior section:

This time, add a filter on the Order Count measure to see only the days where you had more than 350 orders. You get these results:

All of the orders are counted for each day and 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

Sometimes you want to see only a subset of the complete results from your query. You can do this in Looker by setting a row limit, a column limit, or both.

Without a set row limit, Looker supports up to 5,000 rows. Looker supports an unlimited number of columns for unpivoted queries, although we recommend that you have 50 or fewer columns for browser performance. Looker supports up to 200 columns for pivoted queries but sets a default column limit of 50 columns.

Row Limits

When you set a row limit, Looker will only display 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 in these situations; Looker first applies the sort, and then applies the limit. For example, if you only want to see the top five states by number of orders sold, make sure you’re sorting by orders.

If you reach a row limit, you will not be able to sort row totals or table calculations.

Column Limits

If you’ve added a pivot to your report, you can also apply a column limit of up to 200. Looker will warn you if you might be hiding data by setting a column limit that is too low. Again, the sort order of your pivot is important, because Looker first applies the sort, and then applies the limit. For example, if you want to see the five most recent months when orders were created, make sure you’re sorting 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 each count as one column toward the column limit.

Conclusion

Now that you know how to limit your results to the data you’re interested in, learn how table calculations can help you with your analysis.

Top