Forum Discussion

AnupTandale's avatar
AnupTandale
New Member
1 year ago
Solved

How to Filter Date/Time Columns in DirectQuery Mode Using M Query Parameters?

I am working with a table in DirectQuery mode in Power BI, and I need to filter a column of type `date/time` using slicers. Currently, the parameters `startDate` and `endDate` are of type `date` an...
General Comment
Need Help
Show and Tell
Tips and Tricks
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    AnupTandale , Try using

     

    let
    startDateText = Date.ToText(startDate, "yyyy-MM-dd"),
    endDateText = Date.ToText(endDate, "yyyy-MM-dd"),
    KustoParameterDeclareQuery = Text.Combine(
    {
    "declare query_parameters(",
    "startTime:datetime = datetime(", startDateText, " 00:00:00), ",
    "endTime:datetime = datetime(", endDateText, " 23:59:59)); "
    })
    in
    KustoParameterDeclareQuery

     

    The startDate and endDate parameters are kept as date type, and the datetime values are constructed within the query using Date.ToText to format the dates and append the time portion.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi AnupTandale ,

 

Thanks bhanu_gautam  for the quick reply and solution. I have some other ideas to add:
My test steps are as follows:

(1) You can change the parameter to be of type Date/Time. 

(2) Open the “Advancd Editor” reference code:

 

let
    sqlQuery = "SELECT * FROM Calendar WHERE DateField >= '" & DateTime.ToText(StartTime, "yyyy-MM-dd hh:mm:ss") & "' AND DateField <= '" & DateTime.ToText(EndTime, "yyyy-MM-dd hh:mm:ss") & "';",
    Source = Sql.Database("vm0", "DateDatabase", [Query=sqlQuery])
in
    Source

 

(3) Create two slicer tables and then bind the parameters.

Start = CALENDAR(DATE(2024,1,1),DATE(2024,1,31))
End = CALENDAR(DATE(2024,1,1),DATE(2024,1,31))

(4) Result:

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.