Forum Discussion
How do I prevent caching data for each filter used in DirectQuery?
Hi,
In desktop version and with all tables set as DirectQuery, how do I prevent caching data for each filtering? I've noticed that when doing exact the same filtering many times ("that genererates the exact same where-clause"), PowerBI uses the cache instead of a new database query.
We try to use PowerBI as an online query tool. Each time a user filters data, we want the latest data from the db, not "used, old" data from the cache .
Our SQL-server database has kind of those tables:
Order (OrderNo PK, CustomerId FK, ProductId FK, OrderDate, Amount)
Customer (CustromerId PK, Name)
Product (ProductId PK, Name).
If I use the tables as DirecQuery in PowerBI and filter the orders first with Date='2021-08-19', Customer.Name='A', then with Date='2021-08-19', Customer.Name='B' and then again Date='2021-08-19', Customer.Name='A'.
When I examine the generated SQL in Performance Analyzer window, no SQL query is generated the third time, when an old "where-clause" is reused. This should mean changes in Db since last first filtering is not shown. Or is the cache time-limited so after a certain time a new query is generated?
Am I right? Can it be changed by a setting?
/Jerome
If you need 100% fresh data then you should modify your query by adding a random, meaningless filter.
Let's say you know value A is never more than 50. Add a filter for A<100+RAND().
9 Replies
- lbendlin
Super User
You can disable query caching in the dataset settings on the service.
Thank's but need more help:
I use only the desktop version. I can't find the setting you show me. I find in PowerQuery only the two table properties Activate import and Include in report refresh.
/J
- lbendlin
On the desktop you can manually clear both caches and can restrict the permitted size. File...Options and Settings... Options.
