Performance, performance, performance, everyone loves a report that has good performance right? Specially if we are talking about reports using Direct Query, which is the main focus of this post.
While you have some settings in Power BI Desktop that can help you with query reduction (specially useful if you're using Direct Query!), there is no option to add an "Apply all filters" button directly in your report page.
Yes, you can add an "Apply all" button if you use the filter pane, but if you're using slicers directly in your report, the only available option at the moment is to add an "Apply" button to each single slicer, and let's be honest, that doesn't look very nice if you have a lot of filters/slicers in your report.
So, let's explore in more detail the options currently available.
If you go to your in Power BI Desktop and go to Options > Query reduction, you will these options:
So, depending on if you're using slicers directly in your report or the filter pane, there are a few options you can choose from.
If you're using the filter pane, these are the options you have:
Instantly apply basic filter changes
As you can see in the example on the left, there is no button to apply the filters either at the filter level or the pane level.
This means that whenever you do a selection in your filter, these selections will be automatically reflected on the visuals in your report.
This can cause some performance issues, specially if you're using direct query as you're generating loads of different queries that are firing back at your DB every time you make a selection in the filter.
Add Apply buttons to all basic filters to apply changes when you're ready
If you choose this option, you will see that an "Apply filter" button will show for each of your filters in your selection pane.
With the previous option, every time you made a selection in your filter, that selection would be instantly reflected in your visuals, meaning that if you had to select 10 different stations like in this example, this would result in the report "refreshing" the visuals that it is showing 10x.
With the Apply filter, you are able to make your selections, and then it will update your report based on all the selection you've made, so it won't "refresh" your visuals 10x, it will apply the changes only 1 time whenever you click in "Apply filter".
Add a single Apply button to the filter pane to apply changes at once
In this last option, instead of having one "Apply filter" button to each of the filters in your filter pane, you will have a single "Apply" button.
With this, you will be able to make all your filter selections, and these selections will only be reflected in your visuals once you click in the "Apply" button.
If you're using the filter pane, you should definitely consider this option. It's not just more user friendly, it will help you improve the performance of your report, specially if you're using direct query.
If you're using the slicers, these are the options you have:
Instantly apply slicer changes
Good old default option for slicers.
Every time the user makes a selection in any slicer, that selection will be automatically reflected in the visuals in your report.
Add an Apply button to each slicer to apply changes when you're ready
As you probably noticed, each one of the slicers from the previous example has now an "Apply" button.
Even though it will improve your performance (again, specially direct query!), it's probably not the most user friendly option, we have only 4 slicers here, imagine if we had 10...
So, here comes the "Apply all" button to slicers idea!
Before I start telling you all my secrets, I will give you sneak peak of how it looks like:
As you can see from the video, there are a few tricks you can do if you want a similar option for slicers as the one you have for the filter pane.
How did I create this? Let's go through the steps below!
1) If you want to have that blurred effect, here is my little trick: I took a screenshot of my report and added the image to a Power Point slide. Then, I added a blur effect to the image and saved it to my local folder to use later in Power BI Desktop.
2) Select all the visuals that are using data from your data model, group them and hide them:
The reason I did this is because I don't want the visuals to be loading in the background whenever the user selects an option in the slicers. If the visuals are hidden, nothing is being loaded/refreshed in the report page until you decide to show those visuals.
3) In this step, I added an "Apply all" button on my "custom filter pane", added the blurred image we created in Power Point before, and also a text box to tell help the users navigate the report.
4) We are now going to add a bookmark. A bookmark is like a screenshot of your report, and this will be the "landing page" your users are going to see when they open this report, meaning that they will only be able to see the results of the selections they've made once they click on "Apply all".
When creating the bookmark, I disabled the option to update the data. This is just to make the life of our users easier, so when they want to change the filters, the previous selection still shows in the slicers.
5) Now, we are going to build the view the users will see when they click on "Apply all".
To do this, we're building another bookmark to show the visuals we've hidden before, and to hide the blurred image and the text box we just added to our report.
To make everything user friendly, we are adding a shape on top of the filters, so that the users can see what's selected, but can't make any new selections. We are also adding a new button to change the filters, so the user has to go back to the main screen to change the slicer selections.
6) Finally, because we've been adding shapes and buttons after we've built the first bookmark, we are just going to tidy everything up so the bookmarks work properly.
7) And the last step, we just need to make those buttons work! So we are going to define the actions for the buttons, so when the user clicks on "Apply all" it will show the report, and when he clicks on "Change filters" it will take him back to the original view with no visuals.
Tip: to test your buttons in Power BI Desktop, press the CTRL while clicking on the button!
I couldn't finish this post without mentioning my amazing colleague Rebecca O'Connor, who was kind enough to allow me to "steal" this trick from her and write about it, thank you!!!
She also has an amazing blog, have a look at her content here: Home | Artful Data Nerd
Hope you enjoyed this little design trick!!
Cheers,
Mara
Interested in learning more about Power BI Report Design? Then the Power BI Report Design Bootcamp is for you!
