How do I prevent caching data for each filter used in DirectQuery?

[jrmkjrm]

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

[lbendlin]

You can disable query caching in the dataset settings on the service.

[jrmkjrm]

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.

[jrmkjrm]

Hi!

Found it, and I tried to set cache size to 0 (despite the warning in the system) to force system to make a new DirectQuery select each time, but it it became impossible to Refresh any data "maximum cahce size exceeded".

I did not found a feature for "clear cache older than x seconds" (for me 0 seconds :)), is there?

Maybe we must live with this: If one need 100% fresh data, one must use Refresh button? A user can of course not remember if a certain filter has been used (on now cached) since opened the report.

[lbendlin · Accepted solution]

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().

[sharif_rakhimov]

I faced same issue reported on top and tried your suggestion -- it doesn't work. Problem is, even when you add functions in your table's underlying query it still runs same query every time -- when query gets executed is when that random number is generated. However, from powerbi's perspective, query string hasn't changed. 

We need more of a solution that will change the query every time it is CALLED, not when it executes.

[Anonymous]

Hi @jrmkjrm ,

Automatic Page Refresh should meet your needs.

For more details please check the document.

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-automatic-page-refresh 

Best Regards,

Jay

[jrmkjrm]

better, but...

If i check for new max(OrderNo) each minute, my current page with a sum of all orders updates correct each minute (real nice!!!). But if I then (re-)filter on the customer with the new order, the old (cached) customer sum is shown. Only the "on screen" filtering was updatet after a minut. With the customer with a new order selected no new page refresh is done after an extra minute (cause no new OrderNo) so I must manually "Refresh" data anyway, to see the new customer sum.

[alano]

@jrmkjrm did you ever get a solution to this? I am running into this issue now.