Forum Discussion
SQL Query generation in Direct Query
Hi JothyGanesan
When using DirectQuery mode in Power BI with Databricks as the source, especially with large tables and RLS (Row-Level Security) enforced at the Databricks level, query performance becomes critical. In such scenarios, applying filters—like date range selections—is essential for reducing data volume. However, Power BI's default behavior when using slicers (such as for selecting a week’s worth of dates) is to generate SQL queries using an IN clause that lists all selected dates individually (e.g., WHERE Date IN ('2025-06-10', '2025-06-11', ...)). While functionally correct, this approach is inefficient for large datasets because it results in unnecessarily verbose queries, and Databricks struggles to optimize those effectively compared to range-based predicates.
Unfortunately, in DirectQuery mode, Power BI does not provide a built-in way to force slicers to translate to a BETWEEN or >= AND <= range query instead of an IN clause. This behavior is by design, based on how slicers communicate selected values to the underlying SQL query generator. The only workaround is to use a custom filter mechanism—such as replacing the slicer with two separate date pickers (start and end date)—and then using a calculated column or measure that interprets those values in a BETWEEN-like logic, which Power BI may then translate into a more optimized query. This is more likely to happen if the filter is applied via a custom visual or within the DAX query that defines the report visual.
Another approach, albeit more advanced, is to redesign the semantic model to introduce calculated columns or parameters that facilitate range-based filtering, or to adjust how filters are passed to Databricks via views or stored procedures—though this may not be feasible with RLS enforced at source.
In summary, while Power BI currently lacks a direct toggle to force BETWEEN instead of IN in slicer-generated queries under DirectQuery, you can often get closer to that behavior by using range pickers or custom filtering logic. Hopefully, future updates may offer more control over query shaping in DirectQuery scenarios.
Thank you Nasif_Azam
Is it possible for you to explain the 6th option of parameter in the view usage? (Custom SQL View in Databricks)
Sure! The 6th option using a parameterized custom SQL view in Databricks to improve how Power BI interacts with your data when using DirectQuery:
Objective
To prevent Power BI from sending inefficient IN clause queries when using slicers with multiple values, and instead leverage optimized BETWEEN filters directly in the SQL layer of Databricks.
What Is a Parameterized View in Databricks?
Databricks (via Spark SQL) doesn't support true parameterized views like traditional SQL Server or Oracle (i.e., you can't pass dynamic parameters directly to a view).
However, you can emulate this behavior in one of these ways:
Approach A: Use a Dedicated View With a Date Range Filter
You create a view that already includes filtering logic based on a static or expected date range (e.g., “last 7 days”):
CREATE OR REPLACE VIEW v_fact_sales_last7days AS SELECT * FROM fact_sales WHERE order_date BETWEEN current_date() - INTERVAL 7 DAYS AND current_date();
Use Case: Great for dashboards always showing recent activity without needing slicers. Power BI's DirectQuery now just fetches data directly without sending complex queries.
Approach B: Use a Control Table for Filtering
If you need dynamic filtering from Power BI, you can simulate parameters by joining with a single-row control table:
-- Assume you have this control table CREATE OR REPLACE TEMP VIEW control_date_range AS SELECT date_start, date_end FROM parameters_table WHERE report_id = 'SalesReport1'; -- Create your view CREATE OR REPLACE VIEW v_filtered_fact_sales AS SELECT f.* FROM fact_sales f JOIN control_date_range p ON f.order_date BETWEEN p.date_start AND p.date_end;
Approach C: Use Power BI to Write Values to a Helper Table
You can use Power BI with a writeback-enabled tool (e.g., Power Apps or a Fabric Warehouse trigger) to write the user's selected date range into a helper table (slicer_values), which Databricks then reads from in the view.
Things to remember
This method doesn't work out-of-the-box with Power BI slicers — you need some workaround to send the selected date range to Databricks.
Ideal for pre-filtered dashboards or static views used in embedded analytics.
Avoid over-complicating unless the performance gains are significant (e.g., in cases with millions of rows and frequent access).
For ultimate control, switch from DirectQuery to Import mode if RLS and performance can be handled in the model. You’ll avoid SQL inefficiencies altogether.
If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.
Best Regards,
Nasif Azam