In the November 2017 release of Power BI Desktop one feature got my attention: Query Reduction for Direct Query Models.
In some cases, sources for Direct Query models are either large (and therefore need some time to bring back the data) or slow (which could maybe be improved by some database tuning :-)).
Before the Nov2017 release, every change in a filter or slicer led to an immediate round trip to the data source which made some of those reports … let’s call it .. not perfect in the context of user experience.
Beginning with the Nov2017 release some options for Query Reduction has been introduced. Those settings can be changed in the File – Options dialog
In this blog post I am focusing on the “Show an Apply button” section. In order to work with these new settings you’ll have to enable them in the dialog.
Slicer Magic (with the Apply button)
If you are developing a new report with an underlying (Direct Query) data model plus enabled Query Reduction options you’ll get an additional Apply button for every slicer you add to your Power BI report:
Initially the Apply button is gray (which means no change in this slicers selection). If you change the slicer selection to another value you’ll see a change in the Apply buttons color.
The buttons color gets yellow but in contrast to a “normal” slicer reaction the visual itself is NOT filtered. This is exactly the behavior we wanted to see: no immediate round-trip to the underlying data source. Whenever you are ready with your selection – hit the Apply button and the query is sent to the data source. The Apply button gets gray again and the visual is filtered.
Filter Magic (Also with Apply button)
The second option in the Query Reduction section focuses on filter selections. If this option is enabled the filter functionality for Direct Query models (and their reports) change in the way that you explicitly have to apply the filter options.
Happy Power BIing,