Date Range "Between" Slicer Values to Advance Editor

[venkateshlprabu]

I am trying to get the Date Range "Between" slicer min and max values selected in the Query in the advanced editor in the Direct Query to use in Where condition in SQL.

I know I can have two separate filters for start date and End date and get those values, but end users doesn't want a drop down for a date range. 

Any ideas anyone ? 

[venkateshlprabu]

Hi @lbendlin ,

Thanks for the reply. This solution works fine for the non date data types. 

But I am seeing this issue specifically for the Date type when you use the "Between" option. When you use the Between option you won't even be able to assign a parameter to the data table.

[lbendlin]

Why not?  You should be able to use MIN and MAX against your Between range.

[venkateshlprabu]

I am able to get those values in a Measure and see. But I need those values to be sent to my direct query through a parameter so I can use them in the WHERE clause. 

[lbendlin]

as I said, in Power Query use List.Min and List.Max against that parameter  when you compose the SQL query.

[venkateshlprabu]

I tried assigning a parameter to my independent Date table and added a slicer with the "Between" option and binded the parameter. When I change the slicer I get the error as "An incompatible filter is used on a column with a parameter mapping"

[lbendlin]

Don't have access to a Direct Query data source at the moment. Will test tomorrow.

[lbendlin]

 you are right, "between"  filters are not supported. 

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

Unsupported filters

• Relative time slicer or filter
• Relative date
• Hierarchy slicer
• Multifield include filter
• Exclude filters / Not filters
• Cross-highlighting
• Drilldown filter
• Cross drill filter
• Top N filter

@venkateshlprabu

Picking individual values does still work. You would have to modify the SQL to use IN rather than BETWEEN. Or you can let Power Query do the work for you

let
    DateList = if Type.Is(Value.Type(DateRange),List.Type) then DateRange else {DateRange},
    Source = Sql.Database(server,database),
    dbo_Dates = Source{[Schema="dbo",Item="Dates"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_Dates, each List.Contains(DateList,[date]))
in
    #"Filtered Rows"

results in a folding query.

[venkateshlprabu]

Let me try this and get back to you. Thanks for your help @lbendlin 

[lbendlin]

This should get you going.

Chris Webb's BI Blog: Handling Multi-select In Power BI Dynamic M Parameters (crossjoin.co.uk)