Power BI Desktop – Query Reduction for Direct Query Models

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

pbiDesktop_Nov2017_02

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:

pbiDesktop_Nov2017_06 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.

pbiDesktop_Nov2017_08

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.

pbiDesktop_Nov2017_10

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,

Wolfgang

About wolfgang

Data Platform enthusiast
This entry was posted in PowerBI. Bookmark the permalink.

1 Response to Power BI Desktop – Query Reduction for Direct Query Models

  1. Pingback: Power BI Query Reduction – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s