Forum Discussion
DirectQuery 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.
Hello markmsc
Thank you rohit1991 for you prompt response.
Thank you for reaching out to the Microsoft Fabric community. We understand you are experiencing an issue with direct query.
Unlike Power BI Desktop, Power BI Service can apply optimizations like query folding or intermediate result caching to enhance performance, which may cause stale data issues. To address this, consider the following steps:
- Apply a relative time filter in Power BI Desktop by selecting the table visual, adding a filter on the Audit Column, and setting it to "In the last 1 day" to ensure real-time data updates.
- Query folding ensures that filtering occurs at the data source level instead of within Power BI. To verify, right-click on the filtered step in Power Query, select "View Native Query", and if it's greyed out, Power BI might be caching the result instead of applying query folding.
- If using a Shared Capacity workspace, ensure that refresh policies allow frequent queries.
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank you!
Hello markmsc
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hello -- no, I have not. I have tried a few different ways of applying the PQ data filter and have turned off every caching setting that seems to exist, but none of it matters. With any filter in place the DQ data is instantly delayed. I will update this thread if I find a solution.
Hi markmsc
Thank you for your reply! Looking forward to your response.
