Power BI Slicers: Sync, Hierarchies and Best Practices

Slicers are the filters report readers can see. Microsoft describes them as interactive filtering controls on the report canvas that narrow the data shown in other visuals and show the current filter state at a glance (Microsoft Learn: slicers). They look simple, but a few behaviours, syncing, hierarchies and especially Select all, decide whether a report filters the way readers expect.

Choosing a slicer type

The date picker setting supports relative selections such as "last full month", which recalculate each time the report loads, so a report opened next month shows next month's data without edits. Dropdowns save space for long lists; tiles and buttons suit a few options that readers change often.

Slicers or the Filters pane?

Use slicers for the handful of filters readers change constantly, since they are always visible. Use the Filters pane for the rest: it is hidden until opened, suits more complex filtering, and report-level filters there apply to every page. Filters at visual, page and report level covers the pane in detail.

Syncing slicers across pages

A Region slicer on page one is no use if page two ignores it. On the View ribbon, select Sync slicers, select the slicer, then choose pages in two columns:

• Sync: the pages where its selection applies.
• Visible: the pages where the slicer appears.

A slicer can be synced to a page without being visible there, and its selections still filter that page, a useful way to carry context without clutter, but confusing if readers cannot see why data is filtered. Label such pages, or keep the slicer visible.

Two separate slicers can also be synced by giving them the same group name under Advanced options in the Sync slicers pane, then choosing whether to sync filter changes, field changes or both. Microsoft notes this helps in composite models, where the same selection is needed across sources; composite models and DirectQuery explains those models.

Hierarchy slicers

Add several fields to one slicer, such as Category, Subcategory and Product, and it becomes a hierarchy slicer: items expand to show the next level, and a parent shows a semi-selected square when only some children are selected (Microsoft Learn: hierarchy slicers). In the Format pane you can change the expand icon to a chevron, plus and minus, or a caret, and reduce the indentation from its default of 15 pixels when space is tight. Rename the slicer header too, since the default title lists every field.

Watch for blank values. A blank at any level creates a ragged hierarchy, and filtering the blanks out removes the whole row from the slicer. Fill blanks in Power Query, for example with "No subcategory", before building the slicer.

What Select all really does

Turn on Show "Select all" option, and selecting it applies no filter at all. Clear one item afterwards and Power BI stores an inverted filter, "is not" the cleared items, rather than a list of everything still ticked. The practical consequence: values added by a later refresh are included automatically. The UCI Online Retail dataset, a year of transactions from a UK online retailer (UCI: Online Retail), makes this concrete. Suppose a report built on data to the end of March 2011 had "everything except the United Kingdom" selected, and a refresh then loads the whole year. This reproduces both stored forms in pandas:

import io
import urllib.request
import zipfile
import pandas as pd

url = "https://archive.ics.uci.edu/static/public/352/online+retail.zip"
with zipfile.ZipFile(io.BytesIO(urllib.request.urlopen(url).read())) as archive:
    raw = pd.read_excel(archive.open("Online Retail.xlsx"), dtype={"InvoiceNo": str, "StockCode": str})
sales = raw[(raw["Quantity"] > 0) & (raw["UnitPrice"] > 0)]
sales = sales.assign(Revenue=sales["Quantity"] * sales["UnitPrice"])

# the slicer's items when the selection was made: data loaded to the end of March 2011
countries_then = set(sales.loc[sales["InvoiceDate"] < "2011-04-01", "Country"])

# after a later refresh loads the whole year, apply both stored forms of "everything except the UK"
is_not_filter = ~sales["Country"].isin({"United Kingdom"})               # Select all, then clear one item
is_filter = sales["Country"].isin(countries_then - {"United Kingdom"})   # every other item ticked one by one

new = sorted(set(sales["Country"]) - countries_then)
print(f"countries when the selection was made: {len(countries_then)}")
print(f"countries after the refresh: {sales['Country'].nunique()}, new: {', '.join(new)}")
print(f"'is not United Kingdom' filter:  {is_not_filter.sum():>6} rows, revenue {sales.loc[is_not_filter, 'Revenue'].sum():>12,.2f}")
print(f"'is' list of the earlier items:  {is_filter.sum():>6} rows, revenue {sales.loc[is_filter, 'Revenue'].sum():>12,.2f}")

countries when the selection was made: 32
countries after the refresh: 38, new: Brazil, European Community, Malta, RSA, USA, Unspecified
'is not United Kingdom' filter:   44981 rows, revenue 1,641,462.46
'is' list of the earlier items:   44095 rows, revenue 1,626,960.53

Six countries arrived after the selection was made. The is-not filter includes them, 886 more rows and 14,501.93 more revenue; an explicit list would silently leave them out. Neither is wrong, but they answer different questions, so choose deliberately: Select all and clear for "everything except", explicit ticks for "exactly these". The same inverted behaviour applies to basic filtering in the Filters pane.

Controlling what a slicer affects

By default a slicer filters every visual on the page, including other slicers. To exempt a visual, select the slicer, choose Edit interactions on the Format menu, and set that visual to None. A common use is a total or benchmark card that should ignore the region slicer.

Faster, friendlier slicing

• Apply all and Clear all buttons, from Insert then Buttons, let readers make several selections and apply them at once, reducing unnecessary queries on pages with many slicers; performance tuning covers other causes of slow pages.
• Paste values into a slicer from Excel or a text file, up to 2,000 items, for exact matches.
• Search helps readers find items in long lists.
• Field parameter slicers switch the measures or dimensions a visual shows; see field parameters.
• Bookmarks can save slicer states as named views; bookmarks and buttons shows how.

Best practices

• Keep slicers in the same place on every page, usually a top band or left column; dashboard design covers layout.
• Limit a page to the few slicers readers actually use; move the rest to the Filters pane.
• Give every slicer a clear header, and use single select where only one value makes sense.

Slicers and filters are part of the data visualization module of the Power BI course.

Sources

• Overview of slicers in Power BI (Microsoft Learn) — Microsoft Learn
• Add multiple fields to a hierarchy slicer (Microsoft Learn) — Microsoft Learn
• Online Retail dataset (UCI Machine Learning Repository) — UCI Machine Learning Repository