Troubleshooting Common Filter Suggestion Issues – Looker Help Center

Filter suggestions are a powerful tool in Looker. It's crucial to understand where they come from and how they work so that you can troubleshoot effectively when filter suggestions do not behave as expected. This article addresses how filter suggestions work, why they may be wrong, and why they may fail to populate.

How do filter suggestions work?

Filter suggestions save time when users type values in filters and ensure that users choose options that exist in the data. When users click in a filter box, a list of suggestions appears below the field:

Where does this list of suggestions come from?

Looker runs a SELECT distinct <field> query against the database to retrieve all possible options for that field. The query looks something like this:

    SELECT DISTINCT <field_name>
    FROM <table>
    WHERE (<field_name> LIKE '%' OR <field_name> LIKE '% %')
    GROUP BY 1
    ORDER BY 1
    LIMIT 1000

When users type characters in the filter box, Looker substitutes the appropriate conditions in the WHERE clause to filter the results. Looker then shows the first 100 of those results in filter suggestions.

Can I change what suggestions are populated?

Developers can change and customize the suggestions that appear with various LookML parameters. See the Changing Filter Suggestions documentation page for more details.

Are suggestions cached?

Yes. By default, Looker caches query results for one hour. You can use the suggest_persist_for LookML parameter to customize the cache length for filter suggestions. The default suggest_persist_for parameter has a default value of "6 hours". Suggestions have their own cache, which cannot be cleared manually from an Explore page. If you need to clear cache for suggestions, here are some options:

If the Explore is cached using a datagroup with a sql_trigger, you can manually reset the cache for the entire datagroup in the Datagroups page in the Looker Admin panel, but this will refresh the cache for all queries persisted using that datagroup.
You can use a suggest_persist_for parameter at the field level and set it to "0 seconds" to bust the filter suggestion cache for that field.
Cache is global for all users. One user refreshing cache for suggestions will affect the results other users see.

Why are my filter suggestions wrong?

Now that we understand how filter suggestions are populated, we can determine why they may be wrong. The most common reason is because the data has changed or updated between the time the filter suggestions were cached and the time the wrong results were noticed.

For example: User A runs an Explore first thing in the morning, and selects some filter values from the suggestion drop-down. Then the database's ETL process finishes a half hour or so later, before User B views the same Explore and wonders why the suggestions are incorrect. The reason for the disparity is that the cached suggestion query did not update with the database's newly completed ETL process and therefore showed unexpected results.

If this is the case, you can refresh the suggestions cache using methods described in the section above.

Why are filter suggestions not populating?

There can be several reasons for filter suggestions not populating. The following troubleshooting steps highlight potential causes:

1. Check what type of filter it is.

If this is for a legacy dashboard or LookML dashboard (rendered as a either a legacy or non-legacy dashboard) filter, make sure the filter type is Field. Other filter types will not populate suggestions.

Make sure the filter field is of type: string in its LookML definition. Filters on number type fields will not populate suggestions.
Is it a matches (advanced) filter? Matches (advanced) filters require Looker expressions, so suggestions will not populate.

2. Check if there is an `access_filter` or a `sql_always_where` restricting suggestions.

Typically, when sql_always_where or access_filter is used, filter suggestions are restricted for that Explore. This prevents users from seeing a filter suggestion that they cannot access. If you are certain that there are no possible values in a particular dimension or filter field that would reveal sensitive information, you can use bypass_suggest_restrictions to re-enable filter suggestions.

3. Check if there is a `suggest_dimension parameter`.

When the suggest_dimension parameter is used, the filter suggestions will not populate unless the suggested dimension is being referenced in an Explore with that dimension's view defined as the Explore's base view.

For Explores where the suggested dimension's view is not the base view, add the suggest_explore parameter, referencing the Explore where that view is the base view.

4. Check if there is an attempt to load suggestions when clicking or typing into the filter

If not, then Looker isn't trying to populate suggestions. Check that the conditions of step 1 are met, and that suggestions are not turned off at the field, view, or explore level (with sql_always_where or access_filter) in LookML. Note that Hadoop dialects will add suggestions: no on all view files by default.

If yes, proceed to step 5.

5. Check the Chrome Network Console.

The <href="https://developers.google.com/web/tools/chrome-devtools/network">Chrome Network Console may highlight an error with the query itself or show whether there are results returned from cache.

Open the Network tab on your browser with the shortcut Ctrl + Shift +J (on Windows) or command + option + J (on Mac), or by selecting View -> Develop -> Developer tools from the Chrome options bar at the top of the browser.
Click in the filter box on your Look, Explore, or dashboard.
The Developer tools panel should display a request for the filter suggestions, which you can click for more information.
The headers will surface the internal API request Looker is making to retrieve the suggestion values. For example:

<<yourinstance>>/api/internal/models/the_looks/views/order_items/fields/users.state/suggestions?term=
For example, the status code below for the suggestions is 404 Not Found:
- Click the response for this request for more details. In this case, we can see that the suggestions are failing because the field can't be found:
- If there are no errors, but also no suggestions when expected, check to see if the suggestion query is pulling from cache (cache: true in the Network Console) — this may suggest that the cache needs to be busted, using a suggest_persist_for parameter on the dimension that is serving suggestions.

6. Find evidence of the suggestions query that Looker is trying to run.

Use the i__looker History Explore to find the most recent queries. Click on history.created_time, history.source, query.sql_text, and user_name; and filter on history.created_time "is in the past 5 minutes". You should see a query with a "Suggest Filter" Source.

Check the Queries page in the Looker Admin panel to make sure the query generating the filter (Source will say "Filter Suggestion") is not generating an error. Click on the query's Details button and select the Open in SQL Runner option. Double check that the SQL is the same as the SQL in the generated SQL tab of the Explore. If you notice that it is different, check to make sure that you are not using Liquid parameters or templated filters:

If the query requires a templated filter input to run, no filter suggestions will populate.
If the query uses a parameter with a default_value, that value will be inserted in the filter suggestion query. In this scenario, the filter suggestion query will not dynamically update based on the user input. Depending on the default value, this can either cause no filter suggestions or wrong filter suggestions. Instead, consider using linked filters in a dashboard.

What now? Contact us!

Now that you have a better understanding of where filter suggestions come from, why they may be wrong, and why they may not be populating, please do not hesitate to reach out to your Looker contact, or Looker Support. We are here to help you through this, to make sure you are having the best possible Looker experience.

Articles in this section