Result Cache for Direct Query Mode

[Anonymous]

When you expand a summary row in a Matrix visual in Direct Query mode, it triggers a fresh query -- even when it should be unnecessary.  This is creating a terrible user experience when the query takes at least a few seconds.  We should be able to query the data once, and collapse/expand without triggering a fresh query.  Why is the result not cached?  (In my example, the query result is small (<200 rows) or medium (<20,000 rows), but the backing table is very large which is why I prefer Direct Query mode over Import mode, but this performance issue is significant.

I found this blog post that explains it well: Tableau vs PowerBI behavior in Direct Query Mode, Result Cache 

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

For DirectQuery mode, upon load, no data is imported into the Power BI store. Instead, upon building a visual within Power BI Desktop, queries are sent to the underlying data source to retrieve the necessary data. The time taken to refresh the visual depends on the performance of the underlying data source.

Visuals, or entire report pages, can be pinned as Dashboard tiles. To ensure that opening a dashboard is fast, the tiles are automatically refreshed on a schedule, for example, every hour. The frequency of this refresh can be controlled, to reflect how frequently the data is changing, and how important it's to see the latest data. When opening a dashboard, the tiles reflect the data at the time of the last refresh, and not necessarily the latest changes made to the underlying source. You can refresh an open dashboard to ensure it's current.

For more details, please refer to:

Using DirectQuery in Power BI - Power BI | Microsoft Docs 

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.