How to handle multiple date columns with a single month filter in Apache Superset (Power BI USERELATIONSHIP use case)

[sreekanthpamujula9-byte]

Background

We are migrating reports from Power BI to Apache Superset.

In Power BI, we use a single Date dimension table with multiple relationships to a fact table that contains multiple date columns, such as:

• enquiry_date
• test_drive_date
• conversion_date
• booking_date

Only one relationship (with enquiry_date) is active.
Other relationships are inactive and are activated at query time using the USERELATIONSHIP() DAX function.

---

Current behavior in Power BI

When a user selects a specific month (for example, December of the current year) from a single date slicer:

• Enquiry metrics are filtered using enquiry_date
• Test drive metrics are filtered using test_drive_date
• Conversion metrics are filtered using conversion_date
• Booking metrics are filtered using booking_date

All metrics respond correctly to the same month selection, even though only one relationship is active.

---

Problem in Apache Superset

In Apache Superset:

• We can define only one time column per dataset
• There is no concept of active/inactive relationships
• There is no equivalent of Power BI’s USERELATIONSHIP() function

Because of this, we are unable to reproduce the same behavior where:

• A single month filter controls multiple metrics
• Each metric uses a different date column from the same table

---

Question

How should this type of requirement be handled in Apache Superset?

Specifically:

• How can we apply one common month filter
• While allowing different metrics to be evaluated against different date columns from the same table

We are using PostgreSQL as the backend database.

[dosubot[bot]]

Hi @sreekanthpamujula9-byte! I'm Dosu and I’m helping the superset team.

Superset doesn't natively support the Power BI USERELATIONSHIP pattern—there's no built-in way for a single dashboard time filter to control multiple metrics, each using a different date column, within the same chart or dataset. Only one temporal filter can be active per chart/dataset at a time, and there's no concept of active/inactive relationships between a date dimension and multiple fact table columns [source].

However, you can approximate this behavior using Jinja templating in virtual datasets or custom SQL. Superset provides the get_time_filter('column_name') macro, which lets you apply the dashboard's time filter to any date column you specify in your SQL. This means you can define metrics or queries where each metric uses a different date column, but all respond to the same dashboard time filter [source].

Example for PostgreSQL:

Suppose your fact table is leads with columns enquiry_date, test_drive_date, conversion_date, booking_date. In a virtual dataset, you could write:

-- Enquiry metric
SELECT COUNT(*) AS enquiry_count
FROM leads
WHERE 1=1
{% set tf = get_time_filter('enquiry_date', remove_filter=True) %}
{% if tf.from_expr %} AND enquiry_date >= {{ tf.from_expr }} {% endif %}
{% if tf.to_expr %} AND enquiry_date < {{ tf.to_expr }} {% endif %}

-- Test drive metric
SELECT COUNT(*) AS test_drive_count
FROM leads
WHERE 1=1
{% set tf = get_time_filter('test_drive_date', remove_filter=True) %}
{% if tf.from_expr %} AND test_drive_date >= {{ tf.from_expr }} {% endif %}
{% if tf.to_expr %} AND test_drive_date < {{ tf.to_expr }} {% endif %}

-- Repeat for other metrics/columns

Each metric can be a separate chart, or you can UNION them for a summary table. The key is that each query block uses get_time_filter with the relevant date column.

How to set up:

• Enable Jinja templating (ENABLE_TEMPLATE_PROCESSING = True in your config).
• Use virtual datasets or custom SQL queries with the get_time_filter macro.
• Configure your dashboard's time filter to apply to all relevant charts.
• Each chart can use a different date column by referencing it in the Jinja macro.

Limitations:

• This approach requires manual SQL/Jinja logic for each metric.
• You can't have a single chart with multiple metrics, each filtered by a different date column, unless you write custom SQL to UNION or CASE across the columns.
• There's no seamless, UI-driven relationship activation like Power BI's USERELATIONSHIP—it's all handled in SQL.

Alternative:
If you want a single chart with all metrics, consider creating a database view that unpivots your date columns into a "date_type" and "date_value" structure, then filter on "date_value" and group by "date_type".

References:

• Superset SQL templating and get_time_filter macro
• Discussion of Superset's temporal filter limitations
• Jinja context implementation

Let me know if you want a more detailed SQL example or guidance on the unpivot/view approach!

To reply, just mention @dosu.

---

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