Using dashboard filters to filter the query without including column in the result

Get Help
1

Hello,

I want to report on employees being active in the selected time range.
For that I have a joined on and left on column.
I want to have the possibility to take dashboard filter value and add a where clause to say {{datesFromRange}} between joined_on and left_on without showing the column in the resulting query.

I tried to use Models and do something like adding a cte that selects from a dim_dates where {{datesFromRange}} and then filter my main query on dates column from dim_dates between joined on and left on, but I'm unable to map my dashboard filter to the {{datesFromRange}} variable if I don't select the dates column in the results. And if I do, the query becomes extremely slow.

So my question is this:
Is it possible to get the selected value from the dashboard filter and use it to filter my model without showing the filtered column in the result?
If not, is there any workaround to have my "active employees" dynamically based on the selected date.

2

Hi @ramiouanes
I don't understand completely what you are after.
Why can't you just create a question, where you de-select the columns you don't want?
Or even create a SQL question like this?

select id, name, email
from employees
where {{field_filter_range}}
3

Hello @flamber

That means that I have to map {{field_filter_range}} to a column in the employees table, but the problem is that I have a range in employees (Left On and Joined On).
What I want is to only show employees where "one of the dates in the selected date range in the dashboard" is between joined on and left on.
How would that mapping work with a field filter?

Also, would I be able to filter on "name" and "email" in the dashboard without adding more {{variables}} in the query?

4

@ramiouanes Then you'll need to create a lookup table with all dates available - something like

with mycte as (
    select min(date_col) as start, max(date_col) as end
    from lookup_date_table
    where {{field_filter_range}}
)
select employees.*
from employees, mycte
left join another_employees on aeid=eid
where
  employees.date between mycte.start and mycte.end
  or another_employees.date between mycte.start and mycte.end

When this issue is fixed, then you might be able to use a Custom Column via a GUI question, but I'm not completely sure yet:
https://github.com/metabase/metabase/issues/17775 - upvote by clicking :+1: on the first post

5

@flamber I tried your solution and it does work with SQL question having the date range variable.

Now the issue I have is the following:

  1. I want to create a question with all different columns needed (like a model) and then use it to create other cards/questions (counts and whatnot). It looks like once I base any second question on my initial question, the date range filter does not show anymore for some reason.

  2. I want to have the possibility to also add a dashboard filter on "email" and/or "name" or any other column from the result of my query, but it says "no available fields". Do I have to specify a variable per column to be filtered? That's not an option since my initial question will only be used as a source for other questions and these second questions will be put in the dashboard. (I noticed what seems to be a small bug here where in the second question based on the initial one, there are some columns showing if they are dates or they have "name" or "title" in their alias).

So how to propagate variables to questions create based on the initial one?
And how to "dashboard filter" on a column without specifying it as a variable in the SQL query?

6

@ramiouanes I don't have your data, so it's difficult for me to say what you should do.
You would have to create a database view currently.

  1. You cannot parse parameters to nested questions:
    https://github.com/metabase/metabase/issues/6449 - upvote by clicking :+1: on the first post
  2. Sounds like you are seeing this issue:
    https://github.com/metabase/metabase/issues/13186 - upvote by clicking :+1: on the first post
7

@flamber I can upvote but I'm really having an issue now for a delivery in prod and the impact is clearly big!

So I will try to explain the data and needs and see if you can suggest a very much needed workaround.

Table employees:
Columns:

  • Email
  • Name
  • Joined On
  • Left On
  • Office Name
  • Job Title
  • Company

Work Done fact table:
Columns:

  • EmployeeId
  • Date
  • Hours Spent In date

What is needed is simple:

  • How many employees in total
  • How many employees that have spent some hours
  • How many employees that have spent between min & max hours (selected by users).
  • What is the percentage of employee that have spent between min & max hours (selected by users) compared to the total of employees.
  • Being able to dynamically filter all of this by fields such as "Company", "Job Title".
  • Being able to only show active employees during date range selected by users.

I feel it's pretty simple, but I'm unable to do most of these...
It seems impossible to keep it simple and still have answers to these.

The idea is simply to create one query/model to optimize maintenance whenever we need to add/delete columns. This model would serve different questions and these questions will be shown on the dashboards that contain filters.

How to answer these questions with a quick, simple to maintain dashboard?
Any ideas, because to be honest I'm lost with Metabase :stuck_out_tongue:

8

@ramiouanes Many people read these public forum topics - I always write upvote or add links to things, so other people don't just read the topic with nowhere to find further information.

It's a lot easier if you write a hardcoded (without any filters) query that shows all the filtering that you expect users to be able to do. Then figuring out what to replace with variables becomes a lot easier.

Specially the last one, "only show active employees during date range", sounds tricky and would depend on the database type you are querying.

And don't think about maintenance/optimization right now. You can look at that later.

9

Hello @flamber,

The task at hand is exactly maintenance and optimization.
We already have the report on prod, but the underlying data was a mess because we developed it to adapt to Metabase.

Now what we have done is improved the model and based data structuring on BI best practices, and we want to be able to have one model that will answer different questions and not have to change all questions whenever we want to change anything in the data... I do need to think about optimization/maintenance right now...

And I do not know all the filters users want to use yet! I want some of them.
What if I want it to be changed in the future? What if a user wants to add a filter on the dashboard directly by themselves?

The way filters work are really inefficient currently. We should have the possibility to simply use the filters values from Dashboard in custom expressions and the the query without having to map to a column. We should also have the simple possibility to filter all columns in a query... It's already there in the resulting query.

I think, the way metabase currently functions, it is really hard to answer any simple standard needs (e.g active employees) without creating unmaintainable labyrinth...