Looker Filter Suggestions not passing Parameter into Query

[matt_jackman-16]

We’ve just started experiencing an error on the Filter Suggestions call when trying to pull up suggestions in Looker.

We have a table that is partitioned by date, so we always pass a date value (via a parameter in the sql_always_where clause) into the explore to ensure only a single date is selected. This works great for the Looks/Dashboards, but recently our suggestions stopped working on our explores.

The API response indicates that the date partition is not being satisfied because the query that is being generated to pull back the unique suggestions is not capturing injecting the date parameter value into the sql for the suggestion query.

It’s like this on the Look Query:

select status
from account
where account.date = cast(TIMESTAMP(‘2023-02-06 00:00:00’) as date)
group by 1
limit 500

The bolded date value is from our parameter selection.

It looks like this on the suggestion query:

select status
from account
where account.date = cast(NULL as date)
group by 1
order by 1
limit 1000

You can see that the suggestion query is not picking up the user parameter.

Any ideas?

[javihm77]

I have exactly the same problem, we partitioned the Big Query table by date and added clusters, I updated my derived table to filter by date and suddenly the filter suggestions stopped working, anyone found how to solve this?

[TomLoizzo]

You may need to define a default date range if the parameter is not selected Something like if 

COALESE(Parameter Value, CURRENT_DATE()) 

The Filter suggestions run the SQL independent of paramter selections from my understanding just a SELECT DISCTINCT. So without somekind of default as the parameter this will only return values where the parameter value is null 

[jcaitham]

Also have this problem, but it's not possible to choose a reasonable default for my parameter, which makes filter suggestions completely non-functional.  Has anyone found a generalized fix/workaround yet?  

[pitaroFGH]

We found a workaround by using `suggest_dimension` and `suggest_explore` in the field you want to see the suggestion for. The view, where the field is in, then has to be joined in another explore where you don't use any parameters. Point `suggest_explore` to that explore then.

Basically, you're outsourcing the suggestion query to another explore, where you don't use parameters in the query.