GitHub user dosubot[bot] added a comment to the discussion: How to handle multiple date columns with a single month filter in Apache Superset (Power BI USERELATIONSHIP use case)
<!-- Greeting --> Hi @sreekanthpamujula9-byte! I'm [Dosu](https://go.dosu.dev/dosubot) and I’m helping the superset team. <!-- Answer --> 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](https://github.com/apache/superset/issues/33725)]. 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](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/docs/docs/configuration/sql-templating.mdx#L12-L463)]. **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: ```sql -- 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](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/docs/docs/configuration/sql-templating.mdx#L12-L463) - [Discussion of Superset's temporal filter limitations](https://github.com/apache/superset/issues/33725) - [Jinja context implementation](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/jinja_context.py#L287-L1040) Let me know if you want a more detailed SQL example or guidance on the unpivot/view approach! <!-- Dosu Comment Footer --> *To reply, just mention [@dosu](https://go.dosu.dev/dosubot).* --- <sup>How did I do? [Good](https://app.dosu.dev/response-feedback/ddccca4a-a8f2-4ff1-861a-d86241e5ff05?feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/ddccca4a-a8f2-4ff1-861a-d86241e5ff05?feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/ddccca4a-a8f2-4ff1-861a-d86241e5ff05?feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/ddccca4a-a8f2-4ff1-861a-d86241e5ff05?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/ddccca4a-a8f2-4ff1-861a-d86241e5ff05?feedback_type=hallucination) | [Report 🐛](https://app.dosu.dev/response-feedback/ddccca4a-a8f2-4ff1-861a-d86241e5ff05?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/ddccca4a-a8f2-4ff1-861a-d86241e5ff05?feedback_type=other)</sup> [](https://app.dosu.dev/a28d3c7e-a9d3-459e-9fb6-3a6f9ff4f357/ask?utm_source=github) [](https://cursor.com/link/prompt?text=Superset%20doesn%27t%20natively%20support%20the%20Power%20BI%20USERELATIONSHIP%20pattern%E2%80%94there%27s%20no%20built-in%20way%20for%20a%20single%20dashboard%20time%20filter%20to%20control%20multiple%20metrics%2C%20each%20using%20a%20different%20date%20column%2C%20within%20the%20same%20chart%20or%20dataset.%20Only%20one%20temporal%20filter%20can%20be%20active%20per%20chart/dataset%20at%20a%20time%2C%20and%20there%27s%20no%20concept%20of%20active/inactive%20relationships%20between%20a%20date%20dimension%20and%20multiple%20fact%20table%20columns%20%5B%5Bsource%5D%28https%3A//github.com/apache/superset/issues/33725%29%5D.%0A%0AHowever%2C%20you%20can%20approximate%20this%20behavior%20using%20Jinja%20templating%20in%20virtual%20datasets%20or%20custom%20S QL.%20Superset%20provides%20the%20%60get_time_filter%28%27column_name%27%29%60%20macro%2C%20which%20lets%20you%20apply%20the%20dashboard%27s%20time%20filter%20to%20any%20date%20column%20you%20specify%20in%20your%20SQL.%20This%20means%20you%20can%20define%20metrics%20or%20queries%20where%20each%20metric%20uses%20a%20different%20date%20column%2C%20but%20all%20respond%20to%20the%20same%20dashboard%20time%20filter%20%5B%5Bsource%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/docs/docs/configuration/sql-templating.mdx%23L12-L463%29%5D.%0A%0A%2A%2AExample%20for%20PostgreSQL%3A%2A%2A%0A%0ASuppose%20your%20fact%20table%20is%20%60leads%60%20with%20columns%20%60enquiry_date%60%2C%20%60test_drive_date%60%2C%20%60conversion_date%60%2C%20%60booking_date%60.%20In%20a%20virtual%20dataset%2C%20you%20could%20write%3A%0A%0A%60%60%60sql%0A--%20Enquiry%20metric%0ASELECT%20COUNT%28%2A%29%20AS%20enquiry_count%0AFROM%20leads%0AWHERE%201%3D1%0A%7B%25%20set%20tf%20%3 D%20get_time_filter%28%27enquiry_date%27%2C%20remove_filter%3DTrue%29%20%25%7D%0A%7B%25%20if%20tf.from_expr%20%25%7D%20AND%20enquiry_date%20%3E%3D%20%7B%7B%20tf.from_expr%20%7D%7D%20%7B%25%20endif%20%25%7D%0A%7B%25%20if%20tf.to_expr%20%25%7D%20AND%20enquiry_date%20%3C%20%7B%7B%20tf.to_expr%20%7D%7D%20%7B%25%20endif%20%25%7D%0A%0A--%20Test%20drive%20metric%0ASELECT%20COUNT%28%2A%29%20AS%20test_drive_count%0AFROM%20leads%0AWHERE%201%3D1%0A%7B%25%20set%20tf%20%3D%20get_time_filter%28%27test_drive_date%27%2C%20remove_filter%3DTrue%29%20%25%7D%0A%7B%25%20if%20tf.from_expr%20%25%7D%20AND%20test_drive_date%20%3E%3D%20%7B%7B%20tf.from_expr%20%7D%7D%20%7B%25%20endif%20%25%7D%0A%7B%25%20if%20tf.to_expr%20%25%7D%20AND%20test_drive_date%20%3C%20%7B%7B%20tf.to_expr%20%7D%7D%20%7B%25%20endif%20%25%7D%0A%0A--%20Repeat%20for%20other%20metrics/columns%0A%60%60%60%0A%0AEach%20metric%20can%20be%20a%20separate%20chart%2C%20or%20you%20can%20UNION%20them%20for%20a%20summary%20table.%20The%20key%20is%20th at%20each%20query%20block%20uses%20%60get_time_filter%60%20with%20the%20relevant%20date%20column.%0A%0A%2A%2AHow%20to%20set%20up%3A%2A%2A%0A-%20Enable%20Jinja%20templating%20%28%60ENABLE_TEMPLATE_PROCESSING%20%3D%20True%60%20in%20your%20config%29.%0A-%20Use%20virtual%20datasets%20or%20custom%20SQL%20queries%20with%20the%20%60get_time_filter%60%20macro.%0A-%20Configure%20your%20dashboard%27s%20time%20filter%20to%20apply%20to%20all%20relevant%20charts.%0A-%20Each%20chart%20can%20use%20a%20different%20date%20column%20by%20referencing%20it%20in%20the%20Jinja%20macro.%0A%0A%2A%2ALimitations%3A%2A%2A%20%20%0A-%20This%20approach%20requires%20manual%20SQL/Jinja%20logic%20for%20each%20metric.%0A-%20You%20can%27t%20have%20a%20single%20chart%20with%20multiple%20metrics%2C%20each%20filtered%20by%20a%20different%20date%20column%2C%20unless%20you%20write%20custom%20SQL%20to%20UNION%20or%20CASE%20across%20the%20columns.%0A-%20There%27s%20no%20seamless%2C%20UI-driven%20relationship%20activation%20l ike%20Power%20BI%27s%20USERELATIONSHIP%E2%80%94it%27s%20all%20handled%20in%20SQL.%0A%0A%2A%2AAlternative%3A%2A%2A%20%20%0AIf%20you%20want%20a%20single%20chart%20with%20all%20metrics%2C%20consider%20creating%20a%20database%20view%20that%20unpivots%20your%20date%20columns%20into%20a%20%22date_type%22%20and%20%22date_value%22%20structure%2C%20then%20filter%20on%20%22date_value%22%20and%20group%20by%20%22date_type%22.%0A%0A%2A%2AReferences%3A%2A%2A%20%20%0A-%20%5BSuperset%20SQL%20templating%20and%20get_time_filter%20macro%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/docs/docs/configuration/sql-templating.mdx%23L12-L463%29%0A-%20%5BDiscussion%20of%20Superset%27s%20temporal%20filter%20limitations%5D%28https%3A//github.com/apache/superset/issues/33725%29%0A-%20%5BJinja%20context%20implementation%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/jinja_context.py%23L287-L1040%29%0A%0ALet%20me%20know%20i f%20you%20want%20a%20more%20detailed%20SQL%20example%20or%20guidance%20on%20the%20unpivot/view%20approach%21) [](https://go.dosu.dev/discord-bot) [](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/apache/superset/discussions/36846) GitHub link: https://github.com/apache/superset/discussions/36846#discussioncomment-15352104 ---- This is an automatically sent email for [email protected]. To unsubscribe, please send an email to: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
