Category filters native SQL "no valid fields"

I have a question about the dashboard filters.

I have a dashboard that has a single Naive SQL as it’s source. The database for that native SQL is a postgress database.
But when I want to add a category filter all I get are invalid fields.
Why is that?

There are no variables added to the SQL, but I did try that once, but all I got was still “no valid fields”.
What am I doing wrong?

Hi @DannyK
To make use of filters with Native/SQL questions, you have to setup the variables/parameters:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html
Once they’re setup and you save the question, then you should be able to connect filters on the dashboard.

Can you screenshot the question, showing the query and variable sidebar?
Which version of Metabase?

This is a screenshot of the lower part of the query containing the variables/parameters.

Version: 0.33.5.1

@DannyK
And what type of filter are you adding to the dashboard, Other Categories?

Yes, a categories filter.
The filter is based on columns added in the SQL so I can’t use a field filter.

@DannyK Which version of Metabase? And which database are you querying?
Could you try to simplify your query, just as a test, and post that, so I can try to reproduce?

Hi flamer,
As stated it’s version 0.33.5.1 with a postgress database.

I simplified the query a lot by removing a lot of the different WHEN options or columns, but the variables are the thing that matter most.

SELECT
date,
SUM(impressions) AS impressions,
location,
network
FROM(
SELECT
date_trunc(‘month’, date) as date,
SUM(impressions) AS impressions,
CASE
WHEN ( lower(campaign_name) LIKE ‘%paris%’ OR lower(tactic_name) LIKE ‘%paris%’ OR lower(creative_name) LIKE ‘%paris%’) THEN ‘Paris’
ELSE ‘Other’
END as location,
network
FROM (
SELECT
cast(‘Platform1’ as text) as network,
date,
campaign,
adgroup,
creative,
impressions
FROM
table1
) AS data
GROUP BY
date_trunc(‘month’, date),
location,
network
ORDER BY
date ASC
)org_views
WHERE
date_trunc(‘month’, date) < date(date_trunc(‘month’, now()) )
[[ AND network = {{ platform }}]]
[[ AND location = {{ city }}]]
GROUP BY
date,
location,
network
ORDER BY
date DESC,
location,
network

@DannyK
I’ve tried multiple things, but I cannot reproduce. I’m not sure what’s going on.
Not that I think it will make a difference, but try avoiding the extra spaces:
[[ AND network = {{ platform }}]] => [[AND network = {{platform}}]]

When you go to the dashboard, then open your browser developer Network-tab, and when you add the question to the dashboard, you should see a request like /api/card/XX/query - then copy and paste the response data (you can omit the "data":{...} section)

By the way, do you see any warnings/errors in the browser console, when you’re editing the dashboard and trying to add the question or connect filters?

This is the response (without data):

{“database_id”:11,“started_at”:“2019-11-28T16:14:34.264Z”,“json_query”:{“constraints”:{“max-results”:10000,“max-results-bare-rows”:2000},“type”:“query”,“middleware”:{“userland-query?”:true},“database”:11,“query”:{“source-table”:“card__1021”,“aggregation”:[[“sum”,[“field-literal”,“impressions”,“type/Integer”]]],“filter”:[“time-interval”,[“field-literal”,“date”,“type/DateTime”],-30,“day”,{}]},“parameters”:[],“async?”:true,“cache-ttl”:null},“average_execution_time”:null,“status”:“completed”,“context”:“question”,“row_count”:1,“running_time”:509}

This is what the console log shows when editing/adding the question:

@DannyK Okay, it looks like you’re using a Saved Question and not a Native/SQL question directly - is that correct?
Then you’re likely seeing this issue - upvote by clicking :+1: on the first post:
https://github.com/metabase/metabase/issues/9802
You might also be interested in these issues:
https://github.com/metabase/metabase/issues/11007
https://github.com/metabase/metabase/issues/11409

The nested query is a big source for multiple questions.
I can’t split that up in individual sql’s per question unfortunately.
Thank you for your help and Github links.