🎵 On the eighth day of Christmas, my true love gave to me...
Syncing Slicers Slicing,
Conditional Drill-through Button,
and
Filter Data
There are so many choices for filtering data in Power BI; how do you choose when to use each method? Here are a few of the main filter options:
- Filter data at data source
- Filter data in Power Query
- Filter using the Filter pane:
- Filters on all pages
- Filters on this page
- Filters on this visual
- Filter data using Slicers
- Bonus: Filter data in the data view (note this does not impact the report)
If you never need the data for anything, filtering at the data source is a great option. Typically this is not possible. Data is a hot commodity and surely it will be useful for something someday, right? Well, I'm surely not going to make the decision to axe it.
That's the beauty of Power BI - it can handle large datasets, and has the ability to do filtering for you. The first thing you should do in every report when you Transform Data is Filter your data in Power Query. Choose only the columns you need and filter to select only the rows that are relevant for this report. For example, you may only need data from the last 3 years or data specific to a product of focus in order to analyze faults.
For BAU reporting, use the filter pane. Don't make a new report for every month or each region. Power BI makes it really easy to build in dynamic filtering capability. The filter pane has some fantastic functionality that has developed over the years. It is available to end users of the report which means they can interact with the filters and update the report to show the views and data they care about most. It allows for relative date filtering, report, page and visual level filtering, top N filtering and more.
Finally, if you can't achieve what you need with any of the above filters, use slicers. Slicers provide visual cues to your end user, hierarchical filter functionality, relative date filtering, and the ability to sync slicers across multiple report pages.
Sync Slicers
What does it mean to 'sync slicers' and how do we do it? If you've ever copied a slicer from one Power BI page to another, you will have seen this question:
I did a frantic Google search the first time I saw this - what does it mean when visuals 'stay in sync'? Well, in the case of slicers, it means that when you select a value in the slicer on one page, all pages with that slicer synced will update to that value. This means you don't have to keep selecting the same month or region on every page - Power BI remembers what you last selected. Pretty handy.
If you aren't great with keyboard shortcuts (often the only way to copy/paste in Power BI), the Sync Slicers pane is the best way to take advantage of the Sync Slicers functionality. Turn on the Sync Slicers pane by clicking the 'Sync Slicers' button in the View tab.
In the image above, you'll notice that I'm on the Home page of the report, and I have selected the SantaList[Name] slicer. In the 'Sync Slicers' pane I have ticked the box to 'Sync' this slicer on the 'Check the List' page, but I have NOT ticked the box to make this slicer 'Visible' on the 'Check the List' page.
This mimics that drill-through functionality we were looking for - whatever the user selects on the 'Home' page will be filtered/sliced on the 'Check the List' page. However, I don't want them to be able to make any changes from the 'Check the List' page, nor do I have space to display the SantaList[Name] slicer on the 'Check the List' page. So, by ticking only the box for 'Sync' and not 'Visible' I can filter the data on one page based on a selection made on another.
Note: The 'Sync' box must be ticked on ALL pages where you want the slicer to apply, otherwise it will not work. In other words, if you have 'Sync' ticked on the Home page, and 'Visible' ticked on Home and Check the List pages, you'll see the slicer on both pages, but you will need to reselect the name for each page, and could even select a different name on each page. The slicers are not synced at all (the 'Sync' tickbox you have selected for the Home page does nothing without a friend).
How to:
- Open the SantaList.pbix file.
- Select the SantaList[Name] Slicer visual on the Home page.
- Turn on the Sync Slicers pane.
- Sync the Slicer to the Check the List page.
- Save and test your file.
I thought I'd keep it simple for the first day of the year. All the hard work was done last year with the DAX and buttons, now you can enjoy the fruits of your labor and test out a few different names. Are you on the naughty list? Tune in again tomorrow for the next gift in the 12 Days of Christmas series where we'll delve back into some DAX and learn about SWITCH (and why it can be tidier than IF).
