Multiple Filters in Dashboards

Hi there,
Might be a newbie question but I have gone through the documentation and searched help. If there is more advanced documentation or blog somewhere I welcome it.

Two questions:

  1. I am unable to add two filters to a dashboard with a question that has two joined tables. One table has a date filter and the other table has a drop down text filter. It seems to matter which table I add first as to which filter works but I need both of them to work at the same time.
    That leads me to…
  2. What is best practice for setting up questions if you are developing dashboards for consumers so that you can access features like multiple filters. (Not in the role of an exploratory user.) Should you use custom SQL with variables or custom questions? I have used several other analytics tools to develop for clients so I am used to writing code. I just want to avoid going down the wrong rabbit hole.
    Thank you

Hi @michanne
Which version of Metabase?

  1. What type of question is it, Custom or Native? Should be possible to work with both. If Native, then it might be how you used the parameters, so post the SQL.
  2. It depends. There are cases, where you cannot create a question using the Metabase interface, so in such cases you would use SQL. Otherwise you would try to create questions via Custom interface, which would also allow drill-through, and perhaps it would even be possible for the customer to make their own questions with a little introduction.

I am using the custom interface but I tried the native just now for grins.
This one is custom and allows me to select company but not date. I would need to recreate the one that does the opposite.
SELECT date_trunc(‘month’, CAST(“Fct Assessments”.“start_date” AS timestamp)) AS “start_date”, count(*) AS “count”
FROM “hmn_local”.“dim_users”
LEFT JOIN “hmn_local”.“fct_assessments” “Fct Assessments” ON “hmn_local”.“dim_users”.“user_id” = “Fct Assessments”.“user_id”
WHERE (lower(“hmn_local”.“dim_users”.“company”) like ?)
GROUP BY date_trunc(‘month’, CAST(“Fct Assessments”.“start_date” AS timestamp))
ORDER BY date_trunc(‘month’, CAST(“Fct Assessments”.“start_date” AS timestamp)) ASC

This one is native and allows me to select date but not company:
SELECT “hmn_local”.“fct_assessments”.“a” AS “a”, “hmn_local”.“fct_assessments”.“start_date” AS “start_date”, “hmn_local”.“fct_assessments”.“user_id” AS “user_id”, “Users”.“company” AS “company”, “Users”.“user_id” AS “user_id_2”
FROM “hmn_local”.“fct_assessments”
LEFT JOIN “hmn_local”.“dim_users” “Users” ON “hmn_local”.“fct_assessments”.“user_id” = “Users”.“user_id”
LIMIT 1048576

This is custom SQL. It does allow me to do both but I haven’t played enough to know if I can use a different format such as “last month”. It also doesn’t provide a drop down for company which is not going to fly.
select a.assessment_id,a,b,c,d,u.user_id,u.company,to_CHAR(a.start_date,‘YYYY-MM-DD 00:00:00’)::timestamp as start_date
from hmn_local.fct_assessments a
join hmn_local.dim_users u on a.user_id=u.user_id
where end_date is not null
and start_date between {{begin}} and {{end}}
[[and company = ({{company}};]]

Hi @michanne

Which version of Metabase? Which database are you querying?

I would think that the Custom questions where you cannot assign a filter, that’s because your Data Model uses a Field Type not supported for that?
https://metabase.com/docs/latest/administration-guide/03-metadata-editing.html

And as for the Native SQL question, then you’re using the parameter wrong if it is a Field Filter (a dropdown):
https://metabase.com/docs/latest/users-guide/13-sql-parameters.html#the-field-filter-variable-type

...
where end_date is not null
and {{start_date}}
[[and {{company}}]]

You’re on version v0.33.3

I actually did figure out the field filter and I had it working for company on that simple query but I needed an unpivot and if I try to actually change the filter value I get this:
ERROR: invalid reference to FROM-clause entry for table “fct_assessments” Hint: Perhaps you meant to reference the table alias “assessments”. Position: 484

Additionally I cannot get this to aggregate with an average or any aggregate. I don’t even see away to define summary values. Do I need a separate question for each group and aggregation?
I haven’t tried it with a db view yet. I would prefer to know why this doesn’t work. Am I expecting too much?
Query:
select u.company,Quadrant, score as Score
from abc_local.fct_assessments assessments
cross join lateral (values(‘A’,assessments.a),(‘B’,assessments.b),(‘C’,assessments.c),(‘D’,assessments.d)) s(Quadrant, Score)
join abc_local.dim_users u on assessments.user_id = u.user_id
where end_date is not null
and assessments.A is not null
and u.company is not null
and {{start_date}}
and {{company}}

@michanne
You cannot use table aliases, when using Field Filters. This is noted in the documentation as well.
There’s a request for allowing that - go and upvote by clicking :+1: on the first post:
https://github.com/metabase/metabase/issues/3324

okay that is nice but that doesn’t really solve the problem at hand. I added a db view and can’t see it in metabase so I don’t even know if that works.

Basically I need to know what the requirements are to get multiple filters on a dashboards.Custom sql, database views that join tables? Single tables? Primary keys defined in the db? Foreign keys? This is a analytics solution in under an hour. Just saying.

@michanne
When you change your database structure, you’ll need to sync/scan it - it will only do a sync every hour by default.
Admin > Databases > (database) > Sync database schema now

You can add multiple filters to any dashboards, so not sure what the problem is? Are you saying that you cannot add multiple filters to a dashboard?
https://metabase.com/docs/latest/users-guide/08-dashboard-filters.html

Can you provide a video or screenshots of your problem?

Not sure what “analytics solution” is…?

I did re-sync. And waited. The db is local. Just checked again. Having worked on other tools that is the first thing I looked for.

I am unable to create two filters on a dashboard that work on any cards. To clarify: I am joining two tables in a single chart. I read that article. It is addressing having filters that work on multiple cards. If you look at my second comment there are two queries that illustrate the problem.

Update. I can see the view if I x-ray a table. It is in the “related tables” list. I still cannot see it any other way.

@michanne
Do a browser refresh - someones the lists don’t get updated - there’s working being done to fix that.

As for the card Count, is that the query that you posted above? Send a screenshot of the query with the variable sidebar open of filters.

I am using the desktop app. The view finally showed up and I can connect multiple filters. I don’t think this is an ideal solution all the time unless I can pinpoint under what circumstances I need it or if it a bug I need to watch for (Which is fine, I just need to know). So I would still like to know why the others don’t work – I can assess work effort going forward. Does this make sense?

Here is the query for the screenshot. Company works. Date does not. In the earlier posts there are two queries to compare (Unfortunately I don’t have bandwidth to recreate them.)
SELECT date_trunc(‘month’, CAST(“Fct Assessments”.“start_date” AS timestamp)) AS “start_date”, count(*) AS “count”
FROM “hmn_local”.“dim_users”
LEFT JOIN “hmn_local”.“fct_assessments” “Fct Assessments” ON “hmn_local”.“dim_users”.“user_id” = “Fct Assessments”.“user_id”
WHERE ((lower(“hmn_local”.“dim_users”.“company”) like ?)
AND “hmn_local”.“dim_users”.“company” = ?)
GROUP BY date_trunc(‘month’, CAST(“Fct Assessments”.“start_date” AS timestamp))
ORDER BY date_trunc(‘month’, CAST(“Fct Assessments”.“start_date” AS timestamp)) ASC

I can’t associate any dashboard filters to this card. I want to associate start date and company. I get that the filter on average must come after the aggregation so I added the filter on start date after the aggregation and the company filter is before the aggregation. I also tried not defining the filter in the question.

SELECT “source”.“quadrant” AS “quadrant”, “source”.“assessment_id” AS “assessment_id”, “source”.“start_date” AS “start_date”, “source”.“avg” AS “avg”
FROM (SELECT “hmn_local”.“vw_scores”.“quadrant” AS “quadrant”, “hmn_local”.“vw_scores”.“assessment_id” AS “assessment_id”, CAST(“hmn_local”.“vw_scores”.“start_date” AS date) AS “start_date”, avg(“hmn_local”.“vw_scores”.“score”) AS “avg” FROM “hmn_local”.“vw_scores”
WHERE (lower(“hmn_local”.“vw_scores”.“company”) like ?)
GROUP BY “hmn_local”.“vw_scores”.“quadrant”, “hmn_local”.“vw_scores”.“assessment_id”, CAST(“hmn_local”.“vw_scores”.“start_date” AS date)
ORDER BY “hmn_local”.“vw_scores”.“quadrant” ASC, “hmn_local”.“vw_scores”.“assessment_id” ASC, CAST(“hmn_local”.“vw_scores”.“start_date” AS date) ASC) “source” WHERE (“source”.“avg” >= 0
AND date_trunc(‘year’, CAST(“source”.“start_date” AS timestamp)) = date_trunc(‘year’, CAST((CAST(now() AS timestamp) + (INTERVAL ‘-1 year’)) AS timestamp)))
LIMIT 1048576

@michanne
The desktop app - so you mean the Mac App? It’s essentially just a Safari browser without the chrome.
Like I said, there’s work being done to make sure that all lists gets updated correctly without having to do a refresh.

The first query contains company = ?, which makes me think that you are using the query browser and not Native (SQL).

And the second query is also Custom query, but is aggregated, so it seems like Metabase currently doesn’t support filters on aggregated questions.
The workaround would be to create a Native (SQL) using filter variables.

So I’m getting confused - you started out with asking about Native SQL filters, but the last two queries looks like they’re Custom questions.

I presumed it was on Safari because Apple broke v12 it the other day. You answered that it is a bug so I will leave it at that.

My original question was written when i was using only custom queries. I have been trying different methods based on the comments I am getting in this thread plus experimentation based on lack of information about limitations. The goal is to have multiple filters on a dashboard per card. Anything I can do to get two filters on a dashboard that provides a good user experience is what I am trying. But I would prefer not to go down the native sql route because that means anyone that wants to edit or replicate it needs to know sql and it isn’t always going to be me.

Where I am at now: I have accepted that I need to create a views in the database for literally every type of scenario that I want to filter on a dashboard. I can’t use native SQL in metabase because of the problem with aliases so it has to be a view. For obvious reasons this isn’t ideal. Is this on the roadmap?

@michanne
Well, Safari has been having many issues lately, so I don’t know if your problem was from the update or Metabase not updating the list correctly.

Have you checked your Data Model? Since the very first Custom query you posted should work, but I cannot see what the start_date database column type is or what Field Type is defined as in Metabase.

I don’t see why you cannot use Native SQL - just don’t use table aliases for the tables which Field Filters are referencing.
Did you upvote the request for alias support in issue #3324? That’s how features/issues gets prioritized.

As for not being able to link dashboard filters to Custom questions with filtered aggregate data (remember that the Notebook editor is new since 0.33.0, so there are still issues being discovered), then I have a feeling that it’s partly related to how dashboard filters behave with Saved Questions, which is an older issue #9802.
But I have created an issue: https://github.com/metabase/metabase/issues/11007

I upvoted it.
Right now database views are the least problematic t so I am going with that for now. Also I don't want to use SQL if I am passing this to users that don't know SQL or have access to the db schema. Even temporarily because it could work in SQL and fail if I change it.

I have no idea what has changed prior to 0.33.0 because I just downloaded it. I don't even know what the notebook. The best thing I can say is I have been able to figure out most of it... it is just terribly buggy.

Is the reason I can't used save questions due to duplicate filters? See screenshot. There are dup filters on this card. It appears the second filter was added when I mapped it on the dashboard. The same thing with all of them.

@michanne
Before 0.33.0 you could not make joins or do filtering on aggregated data, so the new editor in Custom question (also called Notebook mode) is a lot more advanced. Just use Simple question, if you don’t need that.

The query in your comment 13, you are filtering on aggregated data, which is what is causing the dashboard filter mapping issue. So if you don’t need that type of filtering, but could just filter on the original data, then everything should work.
WHERE ("source"."avg" >= 0 AND date_trunc('year', CAST("source"."start_date" AS timestamp)) = date_trunc('year', CAST((CAST(now() AS timestamp) + (INTERVAL '-1 year')) AS timestamp)))

As for duplicate filters - you can create multiple filters, so they’re not really duplicates.
It’s the first time I’ve heard that filters were added to a question, when it was mapped on a dashboard, so if you can provide steps-to-reproduce that would be great.

By the way, I would highly recommend that you read the User Guide documentation, which gives a pretty good intro on Simple and Custom questions.
https://metabase.com/docs/latest/

I am facing the same issue @michanne did you manage to solve this issue ? I’m unable to create any filters in my case every widget shows up as a "No Valid Fields " whats weird was this was a working functionality in the previous 0.33.1 release , hence most like suggests a regression.

@abhishek
If you are creating questions, where you aggregate the results, then you should follow the this issue - upvote by clicking :+1: on the first post:

1 Like