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` and bound to slicers. My M query for changing type of parameter and adding default time looks like this:

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

Since `startDate` and `endDate` are of type `date`, I can't directly use them to filter columns of type `date/time`. When I attempt to change their type to `date/time` to match the column type, I get the following error:

"This type of transformation is not possible in DirectQuery mode."

Is there an alternative method to filter `date/time` columns using slicers, while staying within the limitations of DirectQuery mode? I’d like to avoid switching to Import mode or making significant changes to the existing setup.

Any suggestions or guidance would be greatly appreciated!

Thank you.

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.

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    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.