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.