Forum Discussion

edgreg's avatar
edgreg
Regular Visitor
7 years ago
Solved

URL filtering by date is broken

Trying to follow the documentation here and add some filters on my URL and having trouble getting date based filters working. I've gotten string based filters working using the eq operator, but I cannot get the gt/ge/lt/le operators to work on dates.

 

I have a date column which is a datetime value from SQL. I have derived a new column in my query to convert this datetime to a string using the function CONVERT(VARCHAR, EndTime, 120) AS szEndTime.

 

I have tried to filter on both the EndTime native DateTime column, as well as my derived szEndTime column:

  • ?filter=Builds/szEndTime eq '2018-10-03 15:00:00' works, but not using the gt/ge/lt/le operators.
  • ?filter=Builds/szEndTime gt 2018-10-03T15:00:00. This does not work.
  • ?filter=Builds/EndTime gt '2018-10-03 15:00:00'. This does not work.
  • ?filter=Builds/EndTime gt 2018-10-03T15:00:00. This does not work.

How can I get my report to filter on dates via the URL?

  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    Hi edgreg,

    I have tested with below format and it could work, I suggest you test again. 

    ?filter=Table/Date gt datetime'2018-09-27T09:55:00'

    Regards,

    Daniel He

6 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    Hi edgreg,

    Based on my test, it could work on my side, you could refer to below steps:

    Sample data and report:

    When I published to the Service:

    Enter the code:

    https://app.powerbi.com/groups/me/reports/cf91650f-9248-40c6-b08a-317a64a011ec/ReportSection?filter=Table1/Date eq 2018-01-01

     

    Result:

     

    Could you please try it again?

     

    Regards,

    Daniel He

    • edgreg's avatar
      edgreg
      Regular Visitor
      7 years ago

      Did you try other operators other than eq?

       

      As I mentioned in my post, the eq operator does appear to work. It's the other operators (gt/lt/ge/le) that do not appear to work correctly.

       

      I cannot access your report to test...I get an error stating "Sorry, we couldn't find that report". I believe this is caused by the "me" portion of the URL https://app.powerbi.com/groups/me/reports/cf91650f-9248-40c6-b08a-317a64a011ec/ReportSection?filter=Table1/Date eq 2018-01-01. I believe me should be a guid.

       

       

       

      • v-danhe-msft's avatar
        v-danhe-msft
        Microsoft Employee
        7 years ago

        Hi edgreg,

        Based on my test, with below code, it could also work on my side:

        https://app.powerbi.com/groups/me/reports/cf91650f-9248-40c6-b08a-317a64a011ec/ReportSection?filter=Table1/Date gt 2018-01-01

        Result:

        I think it may due to your code with your date, could have tried with the below code again?

        ?filter=Builds/szEndTime gt 2018-10-03 15:00:00

        Regards,

        Daniel He