Forum Discussion
Resolver IDirectQuery stale data
Hi markmsc
What you're experiencing is a subtle caching behavior in Power BI Service related to DirectQuery and the way Power Query transformations are applied before the data reaches the model. Even though DirectQuery is designed to fetch live data, when you apply Power Query steps—such as filtering on a column—the service can cache query results for performance optimization, especially when the query is complex or the data is filtered. This caching can cause the data shown in reports to appear stale, updating only periodically rather than instantly, which explains why your filtered table shows outdated data while the other table without such filters returns current data immediately.
When you remove the filter on the audit column in Power Query, the query becomes simpler or more straightforward, causing the service to bypass or reduce caching, and thus data refreshes correctly with current values. The caching mechanism here is intended to reduce load on the source system and improve performance, but it can cause confusion when data freshness is critical.
Unfortunately, Power BI Service doesn’t provide direct controls to disable this caching behavior for queries involving Power Query filters on DirectQuery sources. The best practice is to minimize complex Power Query transformations on DirectQuery tables or move such filtering logic into the source database as a view or stored procedure, which ensures the service queries live data directly without intermediate caching. Alternatively, periodically refreshing the dataset or adjusting incremental refresh policies might help mitigate the effect. Understanding this nuance helps explain why only the filtered table’s data appears stale while the other remains current.
Super UserHi markmsc ,
DirectQuery is built to fetch up-to-date data, but when your Power Query (PQ) transformations (like filtering on an audit column) can’t be folded all the way to the source, Power BI Service starts caching the results. This means you might see current data in Desktop, but after publishing, the Service will sometimes serve up cached results until it decides to refresh.
How to tackle it:
-
Push filters to the source: Try to do as much filtering as possible in your SQL view or stored procedure, not in PQ. For your “last 24 hours” filter, see if you can do it at the database level. This makes the queries fully foldable and helps avoid caching.
-
Check query folding: In Power Query, right-click each step and look for “View Native Query.” If it’s available at every step, you’re good. As soon as it greys out, that step isn’t folding, and you’re likely getting cached results in the Service.
-
Dataset settings in Service: Set Query Caching to “Off” in the Power BI Service settings. If available, turn on “DirectQuery data is always up-to-date.” Consider enabling Automatic Page Refresh (APR) if your workspace allows it.
-
Manual refreshes: If you’re stuck with non-foldable steps, sometimes a manual refresh is needed to force the cache to update.
Caching is mostly unavoidable if your PQ steps don’t fold, no matter the workspace or settings. The best fix is to rework your queries so everything folds to the source otherwise, stale data will pop up whenever the Service caches your filtered results.
- markmsc
Hi Rohit, and thank you for the detailed reply. My thinking is the same as yours, that the defualt query results for this table are being cached. However, I've ensured that Query Caching is disabled for the published model (see screenshot below), and it has no effect. Also I do not seem to have the "DirectQuery data is always up-to-date" setting that you mention. Where would I find that? Thank you.