SQL Query generation in Direct Query

[JothyGanesan]

Hi Team, we are generating the report by connecting to Databricks as the source. The tabe volumes are running into millions.

The business ask is to have filters at date and few other columns. The slicers are being used.

We have to use Direct Query to fetch the data as we have the RLS enablement as well at databricks level depending on the user who uses the report.

We see the query generation using "IN" clause when we give a week filter. Can't we get this as data range "BETWEEN" clause instead? This would help to optimize the query to fetch the results in better response rate.

We are unable to do that switch in the queries generated in direct query 

Kindly help

[Nasif_Azam]

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