Metabase multi-selection filters customization

Metabase is a great tool that offers a lot of functionalities, but sometimes I would like to get even more out of the tools I use. In particular, I am talking about filters. While filters are a valuable and essential functionality to perform dynamic analysis, they present some limitations and can lead to confusing situations, diminishing the end user experience. Below, I show a way to tweak and customize 2 types of filters in Metabase to make them perfectly match your user needs!

Limiting the number of values in drop down filters: 

In this case, I want to display the sales which have been made in a subset of countries (let’s say 10 countries). In the database there is a table listing every country where we sell products. I would like to use this table to build a dropdown filter for my question and show only countries from the subset I'm interested in, instead of including the whole list. With the method above, the dropdown list will list all countries available from the database. I don’t want to scroll to the bottom of the list or type the name every time I want to select France or the UK for example. I want to show only the 10 countries my users are interested in. A solution is to use the available custom dropdown list filter but you have to type the whole list for each new question which alright for a very small list but  nonsensical in the world of automation and hence not the optimal solution.

The most efficient way to use a dropdown filter in Metabase is to make it of the type field filter and map it to the field (in this case country) of the table the data is stored in. Dropdown is not the only categorical filter available in Metabase. Sometimes it’s preferable to define a simpler filter, like a string search field or a custom list of strings from a text filter, although multi selection is not available for this kind of filter.

To solve this problem, we use a dedicated question that lists the countries we are interested in (which we use as a location dimension for other questions). 

There is an easy way to do this in Metabase:

1. As usual, we start by making the filter a field filter.

2. You will see that the dropdown list contains all the countries that are included in the table. To narrow the available choices, create a new question (or native SQL depending on your requirements) which shows only the list of countries that you want in the dropdown list. Save it in the same place where you will save the main question.
3. Click on the Edit button that you see on the right side of Dropdown list in the image above. Then click on From another model or question and select the question you just created which has the list of countries. Select the field (name in my case). Click on Done.

4. Et voilà, when I click on the filter, I see only the 10 countries that I’ve listed. And I can multi-select!

Adapting normal text filters to handle multiple comma-separated values:

Here the data is user created, meaning it does not already exist in this form in the database, so we cannot directly map the filter to a field in a table and make it a dropdown list with multi selection possible (as explained in the previous part).

This issue can be solved with the FIND_IN_SET function from MySQL 8. The way to make this possible is to use a text filter in which users can  type the values they are looking for separated by a comma. Let’s say we analyze the sales of the company grouped by the departments in France. We want to allow the user to type, e.g. 11,12,13,14,15 and have the question show the results for these 5 departments. We had to find a way to retrieve each of the data that belongs to one of these departments, which from a technical perspective is the same as looking for the department of each line of the sales table in the set of user provided departments. 

In France, most of the department numbers are defined as the first 2 digits of the postal / zip code (5 digits). In the following I will follow this rule for the sake of simplicity.

Let’s define:

👉 postcode is the field containing the zip code of the customer buying a product from our shop

👉 department is the custom field derived from postcode containing the department of the sale

• With MySQL 8: LEFT(postcode, 2) AS department 

👉 {{departments}} is the filter of type text (input box format) that can receive multiple department numbers separated by a comma

The function FIND_IN_SET of MySQL 8 takes two parameters and returns the position of the 1st parameter (the string to find) in the 2nd parameter (a set of comma separated strings).  If the 2nd parameter doesn’t contain the first parameter, the function returns 0.

The idea is to find the value of the department  in {{departments}}. If it is not found, FIND_IN_SET will return 0. Thus, I just had to slice the results of my query with the following condition:  

FIND_IN_SET(LEFT(postcode, 2), {{departments}}) > 0

As we can see below, we get exactly the 3 departments typed in the filter. There is no limit to the number of departments we can type in one go.

A limitation, however, is that the string in which the search is performed has to be comma separated. If for some reason, the users want another type of string delimiter for the filter, FIND_IN_SET will not work. You could use REGEXP or declinations of this function to solve this problem.

Another limitation of this technique is that the user has to know all the classes of the data set in order to access full information: the classes are not listed in a dropdown list and the users can forget one in their analysis.  

And that is how we can tweak these 2 types of filters in Metabase to make them show us what we want. It’s a little bit of work, but it works!

References:

https://www.metabase.com/docs/latest/questions/native-editor/sql-parameters#field-filter-syntax

https://www.metabase.com/learn/sql-questions/field-filters

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set

https://dev.mysql.com/doc/refman/8.0/en/regexp.html