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.
Hey JothyGanesan ,
This is a common concern when working with DirectQuery mode in Power BI, especially with large datasets like the millions of rows in your Databricks tables and RLS (Row-Level Security) enforced at the source.
Why is Power BI generating IN instead of BETWEEN?
Power BI’s DirectQuery engine typically uses the IN clause when:
You select multiple distinct values in a slicer (e.g., selecting individual dates),
The slicer is set to list selection instead of a continuous range.
This can happen even if the values represent a range (like a week's worth of dates), resulting in inefficient queries such as:
SELECT ...
FROM your_table
WHERE date_column IN ('2025-06-01', '2025-06-02', ..., '2025-06-07')Instead of:
WHERE date_column BETWEEN '2025-06-01' AND '2025-06-07'
The latter is more optimal, especially for columnar stores like Databricks.
Recommendations to Switch to BETWEEN-style Querying
1. Use a Continuous Date Range Slicer
Default Slicer Behavior: If you're using a slicer on a date field, switch its style from "List" or "Dropdown" to "Between".
In Power BI:
Select the slicer visual.
Under the Visualizations pane, choose the slicer type as "Between" (slider-style).
This encourages Power BI to generate a BETWEEN clause instead of an IN clause.
Power BI doesn’t always guarantee SQL-level optimization just by changing slicer style, but this is the most straightforward way to hint that you're filtering by range.
2. Create a Date Range Parameter Table
If slicer still results in IN, create a custom parameter using DAX:
DateFilter =
VAR MinDate = MIN('DateTable'[Date])
VAR MaxDate = MAX('DateTable'[Date])
RETURN
FILTER('FactTable', 'FactTable'[DateColumn] >= MinDate && 'FactTable'[DateColumn] <= MaxDate)Then use this in a calculated table or measure to control visual-level filters and optimize query folding behavior.
3. Avoid Complex Calculated Columns on the Date Field
Make sure the Date field used in slicers is a direct column from your model or a related date dimension table.
Avoid wrapping it in calculated columns or FORMAT(...) functions in DAX, as this can block Power BI from pushing filters down effectively.
4. Databricks Query Diagnostics
Enable Performance Analyzer or Query Diagnostics in Power BI Desktop:
It will show you exactly what SQL is being sent to Databricks.
This helps confirm whether IN vs BETWEEN is being generated after slicer adjustments.
5. Report-Level Filtering
If the requirement is to apply the same date range across the report:
Use Report Filters (not just slicers) with is after or equal to and is before or equal to logic.
This too may help produce a BETWEEN clause or equivalent predicate in the SQL.
6. Custom SQL View in Databricks
If Power BI's auto-generated queries still don't satisfy you:
Create a Databricks view that accepts parameters or performs pre-filtering using BETWEEN.
Use DirectQuery on that view instead.
Things to Remember:
You can’t directly control how Power BI generates SQL in DirectQuery mode.
But by carefully configuring slicer styles, parameters, and date table modeling, you can influence it.
Using query folding-friendly constructs and avoiding DAX wrapping increases the chances of optimization.
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