Power BI Filters: Visual, Page and Report Level

Filters decide what data every visual shows, and the Filters pane makes it easy to add them. It also makes it easy to hide important decisions from readers, or to apply a condition in a way that answers a different question from the one intended. This guide covers the three levels of filter, the kinds of filter Power BI creates, what authors can lock and hide, how readers' changes persist, and a filtering trap shown on real data.

Three levels, plus drillthrough

Filters live in the Filters pane beside the report canvas, at three levels (Microsoft Learn: add a filter):

Drillthrough pages add a fourth kind, set in the drillthrough well of a detail page; drillthrough and drilldown covers them. Fields placed in a visual automatically become filters for that visual, and you can add other fields as visual-level filters without displaying them.

The kinds of filter Power BI creates

Not every filter in the pane was put there by hand. Microsoft distinguishes eight kinds (Microsoft Learn: filter types):

• Automatic filters come from the fields in a visual; authors can edit, hide, lock or rename them but not delete them.
• Manual filters are dragged into the pane; they can be edited, deleted, hidden, locked, sorted and renamed.
• Include and exclude filters appear when someone uses Include or Exclude on a data point.
• Drill-down and cross-drill filters appear while a visual is drilled down and disappear when it is drilled back up.
• Drillthrough filters arrive with a drillthrough.
• URL filters come from a query parameter in the report's address.
• Pass-through filters are created by Q&A.

Knowing which is which explains why some filters cannot be deleted or renamed.

Lock, hide and name filters deliberately

For each manual or automatic filter, authors can:

• Lock it, so readers can see it but not change it. Use this for rules such as "excludes cancelled orders".
• Hide it, so readers cannot see it at all. Use this only for technical filters, such as removing blank rows. A hidden business rule is a trap for anyone comparing the report with another source.
• Rename it to plain language, such as "Order status" instead of "fct_status_cd".

Filters persist for each reader

In the Power BI service, a reader's changes to filters, slicers and similar settings persist between sessions, so they return to the report as they left it. The Reset to default button restores the author's saved state, and whatever filters are in place when the author saves the report become that default. Save reports with the filters you want most readers to start from.

Filters, slicers and highlighting

Filters remove all but the data you want to focus on, while highlighting, the default when a reader selects a data point, generally keeps the other data visible and dims it (Microsoft Learn: filters and highlighting). Slicers are filters placed on the canvas, visible and obvious to readers. As a rule of thumb, put choices readers make often on the canvas as slicers, and keep fixed rules in the pane, locked and clearly named. Slicers, sync and hierarchies covers slicer design.

The trap: filtering rows or totals

A numeric column dragged into the pane as a visual-level filter applies to the underlying rows of data. A filter on a measure, or on an aggregated value shown in the visual, applies to the totals each data point shows. The same threshold answers different questions. For a UK online retailer's sales (UCI: Online Retail), "quantity greater than 1,000" gives:

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})
services = {"POST", "DOT", "M", "m", "C2", "D", "S", "B", "BANK CHARGES", "AMAZONFEE", "CRUK"}
sales = raw[~raw["InvoiceNo"].str.startswith("C") & ~raw["StockCode"].isin(services)
            & ~raw["StockCode"].str.startswith("gift_") & (raw["Quantity"] > 0) & (raw["UnitPrice"] > 0)]
sales = sales.assign(Revenue=sales["Quantity"] * sales["UnitPrice"])
total = sales["Revenue"].sum()

row_filter = sales[sales["Quantity"] > 1000]                     # filter on the Quantity column: each sales line
units_per_product = sales.groupby("StockCode")["Quantity"].sum()
big_sellers = units_per_product[units_per_product > 1000].index  # filter on the aggregated value per product
value_filter = sales[sales["StockCode"].isin(big_sellers)]
print(pd.DataFrame({
    "Filter": ["Quantity column > 1000 (each line)", "Total quantity > 1000 (each product)"],
    "Products shown": [row_filter["StockCode"].nunique(), len(big_sellers)],
    "Share of revenue": [f"{row_filter['Revenue'].sum() / total:.1%}", f"{value_filter['Revenue'].sum() / total:.1%}"],
}).to_string(index=False))

                              Filter  Products shown Share of revenue
  Quantity column > 1000 (each line)              55             4.0%
Total quantity > 1000 (each product)            1234            80.6%

As a row filter, the condition keeps only individual order lines of more than 1,000 units: 55 products and 4.0% of revenue, a view of bulk orders. As a filter on each product's total, it keeps every product that sold more than 1,000 units in all: 1,234 products and 80.6% of revenue, a view of best sellers. Both are legitimate; they answer different questions. Name the filter so readers know which one they are looking at, for example "Order lines over 1,000 units" or "Products selling over 1,000 units in total".

Good filtering habits

• Put business rules at the report level, locked and named, so every page agrees.
• Keep page and visual filters for presentation, not for changing definitions.
• Avoid hidden filters that change totals, or document them on the page.
• Check totals against the source after adding any filter.
• Prefer measures for rules that belong in the model; DAX FILTER and ALL covers filtering inside calculations.

Power BI dashboard design covers where filters and slicers belong on a page.

Slicers, filters and report interactions are part of the data visualisation module of the Power BI course.

Sources

• Add a filter to a report in Power BI (Microsoft Learn) — Microsoft Learn
• Types of filters in Power BI reports (Microsoft Learn) — Microsoft Learn
• Filters and highlighting in Power BI reports (Microsoft Learn) — Microsoft Learn
• Online Retail (UCI Machine Learning Repository) — UCI Machine Learning Repository