Forum Discussion
DirectQuery "[Posting Date] = latest" issue
Hello,
I am having an issue with a Power Query M formula. I have a formula that filters a column to [Posting Date] = latest. The query is successful, and the column only shows results from today. After completing the rest of the report, the report is published to the service. Everything works fine until the following day, at which point the Posting Date filter breaks. It no longer filters to "latest" but gets stuck on the day that it was published.
Here is the exact code I am using:
= Table.SelectRows(#"Filtered Rows3", let latest = List.Max(#"Filtered Rows3"[Posting Date]) in each [Posting Date] = latest)
For example, I made a report on Friday and published it to the service. Now, on Monday, the filter results in rows filtered to 9/27/19 rather than 9/30/19 (which is now the latest). As soon as I re publish the report, it updates and begins to work again.
Thanks for the support
Michael
Here is the answer:
https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about
Do not use the relative data filtering in Query Editor. It's possible to define relative date filtering in Query Editor. For example, to filter to the rows where the date is in the last 14 days.
However, this will be translated into a filter based on the fixed date, as at the time the query was authored. This can be seen from viewing the native query.
3 Replies
daxCommunity Support
Hi MCKERY,
You said that the filter didn't work in the following day, I want to know whether the Posting Date will update each day? If it will update each day, this might be related to refresh, so if you refresh the dataset, then you view this report in service , will it work well?
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dax ,
Thank you for the response. I thought I had fixed the issue but then it recurred.
The table in question is transactional date, so a new posting date will appear with a new day.
As a work around, I am able to refresh the dataset in PBI Desktop and then re publish the report to the Service. The service will now pull the correct data.
Something is happening where the "latest" pulls a date that then becomes static with future queries on future days instead of pulling an authentic "localnow" date on each query. Being DirectQuery it should send the whole query to the source, no?
Michael
Here is the answer:
https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about
Do not use the relative data filtering in Query Editor. It's possible to define relative date filtering in Query Editor. For example, to filter to the rows where the date is in the last 14 days.
However, this will be translated into a filter based on the fixed date, as at the time the query was authored. This can be seen from viewing the native query.