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.
Hi JothyGanesan
Thank you Nasif_Azam for your detailed explanation about DirectQuery mode in Power BI, especially with large datasets.
Yes, this 6th option using a custom SQL view in Databricks with parameters can definitely help with generating a BETWEEN clause instead of the default IN that Power BI often uses in DirectQuery mode.
You create a simple table in Databricks (say, parameter_date_range) that holds your desired date range like a start and end date. Then, you create a SQL view that joins your main fact table with this parameter table and filters it using a BETWEEN clause.
For example, let’s say your fact table is called fact_sales, and you want to filter it by a date range. First, you create a small parameter table.
CREATE OR REPLACE TABLE parameter_date_range (start_date DATE,end_date DATE);
Set the desired range (could be automated or updated externally)
DELETE FROM parameter_date_range, INSERT INTO parameter_date_range VALUES (DATE('2025-06-01'), DATE('2025-06-07')), Then, create a view using this parameter:
CREATE OR REPLACE VIEW v_filtered_sales AS
SELECT s.*
FROM fact_sales s
JOIN parameter_date_range p
ON s.sale_date BETWEEN p.start_date AND p.end_date.
Now, when Power BI connects to v_filtered_sales using DirectQuery, it will only query rows between the given dates, and Databricks will use a clean BETWEEN clause behind the scenes.
This completely avoids the issue where Power BI sends a long list of IN (...) values, which can slow things down, especially with large tables.
To update the Date Range. You have a few options here depending on how dynamic you want it to be:
- If the range changes infrequently, just update the parameter_date_range table manually or via a Databricks job.
- If it needs to reflect the user's selection in Power BI, then you'd need a small write-back mechanism (for example, a Power App or API call that updates the table when the user selects a date range).
This method ensures Databricks always uses a BETWEEN filter, which is faster and cleaner, and you get full control over how the date filter is applied, outside of Power BI’s auto-generated SQL. Which works well with RLS, large datasets, and lets Databricks handle the heavy lifting.
------------------------------------------------------------------------------------------------------------------------------
If this response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.
Regards,
Akhil.