Custom SQL question with date not working in dashboard

Get Help
1

Hi there,

I am trying to show activity for all users for a given tenant in my application. I explicitly want to see all users including those that have not completed an appointment. Here is the SQL Query:

SELECT tu.user_id, (SELECT email FROM users WHERE id = tu.user_id) AS email,
       COALESCE(COUNT(a.id), 0) AS completed_appointments
FROM tenant_users tu
         JOIN users u ON tu.user_id = u.id
         LEFT JOIN appointments a
                   ON a.user_id = tu.user_id
                       AND a.tenant_id = tu.tenant_id
                       AND a.started_at > {{start_date}}
                       AND a.started_at < {{end_date}}
                       AND a.status = 'COMPLETED'
WHERE tu.tenant_id = {{tenant_id}}::uuid
GROUP BY tu.user_id
ORDER BY completed_appointments DESC;

This works great as a stand alone Custom SQL question. However, when I want to put it into a dashboard and connect it to a dashboard filter, I get an error that says "Add a date variable to this question to connect it to a dashboard filter"

image
image792×388 15.1 KB

It is defined as a date variable in SQL question. The dashboard is also already properly connected to non-custom-SQL Question. I just can't get it to work with Custom SQL questions.

Any guidance on what I can do?
We are on Metabase v0.49.2.

Thanks!

2

You need to setup field filters for reference: