How to select multiple countries in a dashboard filter?

Hi,

I have a dashboard with multiple native SQL questions that select data from different Postgres views. I’ve added a Country field filter for every one of those questions and I’ve also added a Country filter on my dashboard.

The problem I’m facing is that my dashboard’s country filter doesn’t allow me to enter multiple countries. It’s a plain input field. I’ve tried entering my countries as a comma separated list, but it doesn’t work.

Interestingly, if I open any of the SQL questions added to the dashboard, that individual question’s field filter does allow me to enter multiple values.

So my question is: how do I do that within the dashboard?

Thanks.

Hi @sudokai
Which version of Metabase?
You would need to make sure that all Field Filters reference the same table.
You can do that by joining a table with countries, like below, and using countries table as the Field Filter reference:

SELECT * FROM table
LEFT JOIN countries ON countries.id=table.country_id

There’s a few issues open about mixing datasets with dashboard filter dropdowns, but I think this is the most relevant one:
https://github.com/metabase/metabase/issues/8698 - upvote by clicking :+1: on the fist post

Very nice trick! Using your strategy, the dashboard filter changed into a search box :smiley:

I’m using Metabase 0.33.6.

2 quick follow-up questions:

  1. Is there any way to exclude a single country using a dashboard filter?

  2. In order to link a SQL question filter variable to a dashboard filter, do you always need to map that variable to a table or view? For example, the following doesn’t seem to work:

    select * from get_retention(products := ‘{1}’::int[])
    where {{ country_code }};

However if I create a view first, it does work:

create view retention_product_1 as
select * from get_retention(products := '{1}'::int[]);

select * from retention_product_1
where {{ country_code }};

@sudokai
Great it works - hacks are the way to get around certain limitations in Metabase :slight_smile:

  1. I’m not sure I understand. Do you want to exclude the selections made in the filter (that’s possible, by doing a sub-query), or are you asking to not show certain options in the dropdown?
  2. The variable WHERE {{country_code}}, when used as Field Filter, is replaced with SQL code like WHERE schema.table.column IN ('option1', 'option2'), where the reference comes from your selection in the Variables sidebar. So yes, you need to reference the table/view in the FROM syntax. If you use simple filters (Text, Number, Date), then they’re more flexible, but also less advanced.

@flamber My use case is as follows: I want to analyze business metrics at a national level and also at a international level separately. That’s why I was asking if there was a way to quickly exclude a country’s data from the dashboard. I would exclude my local country to get all the international data.

@sudokai
Since I don’t know your query or structure, then it’s a just an idea:

SELECT * FROM table
WHERE 1=1
[[AND table.country_id NOT IN (SELECT countries.id FROM countries WHERE {{exclude_countries}})]]

@flamber Awesome, yes this works! That’s some lateral thinking!