One filter for several columns

Get Help
1

Hi, I have this task i don’t know how to do it, and maybe one of you can help me

I have one table, with lot of columns, 3 of them are:
job1, job2, and job3 are binary, just say “yes” if the person works on that job, or “not” otherwise

What I want is to add one filter on a public dashbord where the person can choose in one filter the jobs “JOB1”, “JOB2” and/or “JOB3”, and not have 3 filters, one for each column

I will be grateful with any advice to help me solve this

2

One way to achieve what you want:
Create a VIEW on that table by adding a column where its value is “job1” if the column job1 has value “yes”, its value is “job2” if the column job2 has value “yes” and so on … . Then build your filter using that column in the VIEW.

3

but i think that view is going to work in the case columns are exclusive to each others

But in this case the individuals have the expertise to do one, two or the three jobs, or none of them.

4

I had assumed the jobs are exclusive.
OK. There may be a simpler solution, but the following does the job (I assume there are three jobs, you can extend it to more jobs):

  • There are 2^3 = 8 combinations of values of the three jobs. We add a column called ‘num’ which assigns a number to each of these combinations. We consider the first three prime numbers 2, 3, 5 for the jobs 1,2,3, and assign a number to each combination by multiplying these primes when the value in that column is ‘yes’. E.g. in a row where job1 is ‘yes’ and job2 is ‘no’ and job3 is ‘yes’ we assign the value 10 (2*5).
  • We want our filter values be ‘job1’, ‘job2’, ‘job3’. The only way I know to achieve this is adding a column that has these values in three rows and null in other columns.

The following example shows the progress so far:

person_name	 job1	job2	 job3	num	filter_values
NULL	         NULL	NULL	 NULL	NULL	job1
NULL	         NULL	NULL	 NULL	NULL	job2
NULL	         NULL	NULL	 NULL	NULL	job3
John	         y	n	y	10	NULL
Joe	         y	n	n	2	NULL
Duke	         y	y	y	30	NULL
Peter	         y	y	n	6	NULL
Jim	         n	y	y	15	NULL
David	         n	y	n	3	NULL
James	         n	n	y	5	NULL
Mich	         n	n	n	1	NULL
  • Now we create a native question in Metabase with the following query:
select *
from test
[[ where person_name in ( select person_name 
                          from test
                          where num % case when (SELECT filter_values FROM test WHERE {{f}}) = 'job1' then 2
                                           when (SELECT filter_values FROM test WHERE {{f}}) = 'job2' then 3
                                           when (SELECT filter_values FROM test WHERE {{f}}) = 'job3' then 5
                                           else 31
                                      end 
                                = 0 
                        )
]]

For the variable “f” choose ‘Field Filter’ pointing to the column “filter_values”.

The above query basically maps the chosen filter value by user to one of the primes 2, 3, 5, and then shows those rows from table where their value in the column ‘num’ is divisible by this prime number.

The limitation is that only one filter value can be chosen at a time. As a result, since selecting no filter is the same as selecting all filters, you need to define a default value for the filter.