DirectQuery not updating on date

[SamTrexler]

I have a report which uses DirectQuery against a SQL Server on-premises database. The query contains this statement for filtering the rows:

#"Filtered Rows" = Table.SelectRows(dbo_Tests, each Date.IsInPreviousNDays([KeyDateTime], 30) or Date.IsInCurrentDay([KeyDateTime])),

This works fine in Desktop. When I publish it to Service, it works correctly on the day I publish it and gives correct results throughout the day. But after that, it is still comparing the "IsInPreviousNDays" and "IsInCurrentDay" against the day I published it. 

The on-premises gateway is configured correctly. Service states that this report uses the gateway and that it is online and available. Cache refresh works correctly on the date it is published. (For example, I published a new version of the report on 5/30 (yesterday) with some changes (and hoping this might be corrected by the changes or fixed in Service), and this morning the report is retriving data through 5/30/2017 11:58:08 PM - but none of the 30,000 or so records from today are retrieved.)

Refreshing the dataset manually does not retrieve today's data, and the scheduled cache refresh is not retrieving today's data - even though "Refresh History" shows scheduled refreshes every hour with Status = "Completed".

I see a post "IsInCurrentDay not updating in DirectQuery mode and Entreprise Gateway " from 12/2015 that suggests hard-coding the query and they would work on getting this to work. Is it still not working 1.5 years later?

DirectQuery is supposed to retrieve up-to-date data and not require a refresh - or else what's the point? Is there a bug in these functions? Is there a workaround to get the results I need? 

Thanks,

Sam

[v-yulgu-msft · Accepted solution]

Hi @SamTrexler,

Currently, it seems that Date.IsInPreviousNDays and Date.IsInCurrentDay fucntion does not work as expected in Direct Query mode. Someone else has submitted this behaviour as an issue here: Date slider range does not update as data changes (using direct query). It describes similar issue as yours. As suggested in this thread, you can work around this issue via moving date filter to a database view. Use this view to schedule refresh source table, then, we can get up to date result in Power BI.

Regards,
Yuliana Gu

[SamTrexler]

Hi @v-yulgu-msft,

Creating a view to limit the dates is an interesting idea. I'll see if they'll let me - this is a high-volume production OLTP database. But for that same reason, creating a view is a good approach: have the database limit what can be retrieved. (That's why I was using the M language - to get the filter included in the query and applied by the database.)

Thanks for the possible workaround. I'll see if I can get it to work - in a test database first, of course..

Regards,

Sam

[SamTrexler]

Hi @v-yulgu-msft,

That worked fine in the test database. I'm working with the DBAs to get it into the Production database. So I am accepting your post as the solution.

However, I hope Microsoft eventually fixes this problem so that we can have a more dynamic solution that can respond to conditions in the M query. This works only if the database knows the calculation in advance, or wants to limit it.