How to use Jinja template for "Inverse Selection" filter type

[natcharam]

I need help with Jinja templating for handling "Inverse Selection" filter type in SQL Lab/Dataset.

Setup:
I have 2 filters for the same column CAMPAIGN_GROUP:

Normal filter (Filter Type: "Filter") - works fine
Inverse Selection filter (Filter Type: "Inverse Selection") - need help with this

What Works - Normal Filter:

{% if filter_values('DEVICE_TYPE') %}
  AND DEVICE_TYPE IN {{ filter_values('DEVICE_TYPE') | where_in }}
{% endif %}
{% if filter_values('CAMPAIGN_GROUP') %}
  AND CAMPAIGN_GROUP IN {{ filter_values('CAMPAIGN_GROUP') | where_in }}
{% endif %}

Problem:

• How do I write the Jinja template to handle the "Inverse Selection" filter type?
• The inverse selection filter appears in the dashboard with the same column name CAMPAIGN_GROUP, but I need it to generate NOT IN logic instead of IN.

Questions:

• How can I distinguish between the normal filter and inverse selection filter in Jinja templates when they use the same column?
• Is there a different function to call for inverse selection filters?
• Does filter_values() work differently for inverse selection filter types?
• Should the inverse selection filter automatically generate NOT IN logic, or do I need to handle this manually?

Expected Behavior:

-- Normal filter should generate:
AND CAMPAIGN_GROUP IN ('value1', 'value2')

-- Inverse Selection filter should generate:
AND CAMPAIGN_GROUP NOT IN ('excluded1', 'excluded2')

Additional Context:

• Both filters target the same CAMPAIGN_GROUP column
• Users need both include and exclude functionality

[dosubot[bot]]

Hi @natcharam! I'm Dosu and I’m helping the superset team.

To handle both normal and "Inverse Selection" filters for the same column in a Jinja template, use the get_filters() macro instead of filter_values(). The get_filters() macro exposes the filter operator (IN or NOT IN), so you can generate the correct SQL logic for each filter type. Here’s how you can do it:

{% for filter in get_filters('CAMPAIGN_GROUP') %}
  {% if filter.op == 'IN' %}
    AND CAMPAIGN_GROUP IN ({{ "'" + "', '".join(filter.val) + "'" }})
  {% elif filter.op == 'NOT IN' %}
    AND CAMPAIGN_GROUP NOT IN ({{ "'" + "', '".join(filter.val) + "'" }})
  {% endif %}
{% endfor %}

This approach works because get_filters() returns a list of filters for the column, each with an op field (like 'IN' or 'NOT IN') and a val field (the values). You can loop through them and generate the appropriate SQL for each filter type.

filter_values() does not distinguish between filter types and will not handle NOT IN logic automatically—you need to use get_filters() for this use case. The NOT IN logic is not generated automatically; you must handle it in your template as shown above. See the code and docstring for get_filters() for more details and examples on this pattern
here.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}