Forum Discussion

SamTrexler's avatar
SamTrexler
Helper IV
9 years ago
Solved

DirectQuery not updating on date

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 Dat...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    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's avatar
SamTrexler
Helper IV
9 years ago

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's avatar
SamTrexler
Helper IV
9 years ago

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.