How to apply filter at dashboard level?

hky404 · October 16, 2017, 9:24pm

Hi there, I would like to apply a date range filter at dashboard level, and also is there a way I can categorize a filter into a drop-down?

at the dashboard level it says 'No valid fields'
here is the code:

SELECT active_listing_count,
mls_system,
rew_property_type,
date
FROM prod.listing_fact_daily
WHERE date BETWEEN {{StartDate}} AND {{EndDate}}
AND mls_system LIKE {{mls_system}}
AND rew_property_type::text LIKE {{rew_property_type}}
GROUP BY 1,
2,
3,
4

vincent · October 17, 2017, 11:41am

is your ‘date’ field recognized as a metabase date instance, or is it just a string ?

hky404 · October 17, 2017, 6:39pm

Hi @vincent thanks for the comment. I changed the date field to the type ‘category’ in the admin panel and everything worked fine as far as date is concerned. Now to the next question, how can I make a field drop-down?

maz · October 17, 2017, 7:05pm

Hey, @hky404. I know we’re also talking about this on github, but I thought I’d answer you here for others’ benefit.

First, regarding your data field, as Vincent asked, is it stored in your database as a text or string field? If not, you shouldn’t need to set it as a Category. If you have a field filter variable, and you link it to a DateTime field (i.e., one that is stored as a DateTime in the database itself), the UI should give you an option to show a date filter widget.
To get a filter widget to be a dropdown, it needs to be mapped to a field with fewer than 300 distinct values. So if, as I’m guessing, that your situation is you have a date field that’s actually being stored as text, you’re probably going to have more than 300 distinct values, in which case the UI is just going to give you a input box.

We recognize that there are still some usability problems with field filters and dropdown filters, and that there are some aspects of this that aren’t super clearly documented, but we’re working to fix these issues.

hky404 · October 17, 2017, 7:15pm

thanks @maz for replying in detail. My date field is stored in the DB as 'date' field not as datetime. So, I think the normal date filter will work fine for me. Ideally, I will try to modify it to a date-range filter instead of doing it like this (my current setup) of two separate filters for 'Start Date' and 'End Date'

for the other two fields, I would love to make them a drop down (as the unique values for both the fields is less than 300) do I need to make changes to my code? OR this is achievable at the dashboard level as I couldn't find anything to achieve that drop-down effect.

maz · October 17, 2017, 8:01pm

I just took a closer look at your query. Yes, you can simplify this by using field filters to dynamically insert SQL in your WHERE clauses, like this

SELECT active_listing_count,
mls_system,
rew_property_type,
date
FROM prod.listing_fact_daily
WHERE {{date_var}}
AND {{mls_system_var}}
AND {{rew_property_type_var}}
GROUP BY 1,
2,
3,
4

Then map date_var to your date field, and you should be given to use the specialized data picker for your filter instead of two separate input boxes. The other two variables should also be set to field filters, and should be mapped to the corresponding fields, and then you should get dropdown filters for both of them, provided they’re marked as Category in the metadata and that they each have fewer than 300 distinct values like you mentioned they do.

Let me know if that works for you.

hky404 · October 17, 2017, 10:25pm

thanks a lot @maz that worked pretty well. I hope this question will serve as reference for others facing the similar issues. Thank you.

maz · October 17, 2017, 10:33pm

Yay, happy to hear that!

Gunjansrivastava · November 9, 2018, 7:30am

Hey Maz,
I have an issue with drop down filters at dashboard level. Following is the problem;

When i close the chrome window or logout from Metabase and relogin, Dashboard dropdown filter values are gone. It acts as input text box. I am using "Region" and "City" filters

What i curretly do is
-> Go to the dashboard. "Region" and "city" dropdown filter does not display any value
-> select one of the question and open the question page ( Native or custom query)
-> select those filters (i.e. "Region" and "City") and fetch the values in dropdown filter at question level

-> go back to dashboard page. You will now see the values appearing

This will be very hard to explain it to my clients. Not sure how best we can handle this ?

Hope i am explained the issue properly, i can explain it in more details if needed

Regards
Gunja

fetanchaud · November 9, 2018, 9:12am

Hi Gunja,
I noticed this happens when cards share a same filter, but question filters are based on distinct data fields (for example same degenerated dimension in different fact tables.

Is it your case ?

If so, You have to create a new reference table for the degenerated dimension containing its values, link (PK/FK) the fact tables to this new dimension table, and modify your filters (and any SQL field filters) accordingly.

Hoping it helps,

Best Regards,
Fabrice Etanchaud
CERFrance PCH

Gunjansrivastava · November 9, 2018, 12:25pm

Hello Fabrice,

I noticed all my questions are custom query and these filters are not applied at question level but directly at dashboard level.

Currently, while trying to fetch values at question level, i only select the relevant “region” and “city” filter temporarily and do not add to the questions.

If we try to create a new reference table, i cannot think of a way to modify any filters and point it to newly created dimension table

Any idea how we can achieve this ?

Thanks
Gunjan

fetanchaud · November 9, 2018, 4:33pm

for example, If for each SQL question

select * from fact_table join dim_table on (dim_table.id_dim = fact_table.id_dim where {{var_dim_table_filtered_column}}

If you define the var_dim_table_filtered_column variable as a Field Filter pointing to the dim_table.filtered_column column

Then your dashboard filter should present a list or a autocomplete list, based on the related setting of the dim_table.filtered_column you mentioned in the metadata panel.

Hoping it helps,

Fabrice

maz · November 12, 2018, 10:16pm

Which version of metabase are you using @Gunjansrivastava? And what kind of data are you querying?

Gunjansrivastava · November 21, 2018, 10:45am

This will help only when i am creating a native SQL query. In case of customer query builder, how can we reference the dimension table ?

Gunjansrivastava · November 21, 2018, 10:46am

I am using 0.30.4 vesion. And these are sales transactional data