All articles

KajoData / Career & business

Power BI filters and slicers: control the scope of a result

I use five orders to explain filter scopes, the difference between an amount and its sum, and how to check slicer interactions.

Kajo RudzińskiPublished Updated 12 min read

A selected stream of cubes passes through a filter and feeds a chart.

You select a region on a chart. One number falls, another chart keeps all its bars, and a third card stays unchanged. Power BI may be behaving exactly as configured. The report author may simply have left you three different interactions to decipher.

I start designing filters with a question: what should the reader be able to compare? I choose the controls afterwards. A slicer covering half the page can look impressive, but the result still needs a clear scope. Here I work from a small table towards deliberate interactions and numbers that can be checked by hand.

Establish what a row means before adding controls

The following data is synthetic. Each row represents one order. Amounts use one arbitrary unit, with no tax, returns or currency conversion. All orders belong to the same period. This deliberately small dataset keeps the explanation visible: you can point to the orders behind any result.

Order Region Category Amount
A North Equipment 600
B North Equipment 700
C North Services 200
D South Equipment 900
E South Services 300

The overall amount is 2700. North contributes 1500 and South 1200. Equipment contributes 2200 and Services 500. I write those figures down before building the report. Later, a card showing 1500 for North has an independent reference. Without one, almost any large, neatly formatted number can look convincing.

I put an overall card, a chart by region and a table by category on one report page. This lets me investigate both a page selection and a click on an individual bar. A single table is enough for the first exercise. I introduce relationships between tables after the simple version makes sense.

Separate the scope of a filter from its control

The Filters pane supports restrictions at visual, page and report level. Microsoft describes these scopes in its guide to adding filters. The scope answers where the condition should apply.

A slicer answers a different question: how does the reader select a condition? I put one on the report canvas when a region or period choice should be immediately visible. The pane and slicers can serve different needs within the same report. There is no need to choose a single approach for every condition.

For this exercise, a visual filter could restrict the category table to Equipment. A page filter could select North. That table would then describe Equipment in North, giving 1300. A card on the same page without the category restriction would show 1500. Both figures have an explanation, but the reader needs to see it. I therefore put the category in the table title.

I avoid report-wide filters chosen simply because the broadest scope feels tidy. One page may describe a selected period while another needs to compare historical periods. A shared restriction can prevent the second page from answering its question.

Filtering an amount differs from filtering its sum

This is the central distinction. A condition on the raw Amount column greater than 1000 asks a different question from a condition on the sum of amounts greater than 1000. Our dataset contains no individual order above that threshold. The first condition therefore finds no orders.

Now consider a visual grouped by category with a filter on a measure that sums amounts. Equipment can remain because its total is 2200. Services, with 500, falls below the threshold. Microsoft’s DirectQuery guidance explicitly illustrates measure filters using categories whose total sales exceed a threshold.

The Filters pane consequently cannot be described as always filtering individual transactions before aggregation. I need to identify the field being filtered and the grouping used by the visual. A label such as Sales is insufficient on its own: it could refer to a source column, an aggregation or an explicit measure.

I prefer names such as Order amount and Sum of order amounts. Those names also improve the requirements conversation. If someone asks for sales above 1000, I ask whether they want large orders or categories with large totals. Each request is reasonable. They require different conditions and produce different answers.

A chart selection can filter or highlight

Cross-filtering narrows the data shown by another visual. Cross-highlighting retains broader context and emphasises the selected contribution. The options available depend on the visual; Microsoft’s interaction documentation explains the distinction and configuration.

Suppose you select North on the region chart. A table filtered to North shows Equipment at 1300 and Services at 200. A highlighted category chart can retain the overall 2200 and 500 while emphasising North’s contribution. These are useful ways to present different aspects of the same selection.

I check Edit interactions on the actual objects instead of memorising a rule that a particular chart type always highlights. Then I select a value with a known result. Changing a visual type later is a good reason to repeat that check. A cosmetic edit can change how a reader understands the selected portion of the data.

Two trays show a selected subset and the same subset highlighted within the whole collection.

Add the first slicer and give it a precise name

In Power BI Desktop, I add a slicer visual and place Region in it. I start with a readable list or dropdown. Microsoft’s slicer visual guide documents the available settings; the arrangement of the editing pane can change between product versions.

I select North and compare the card with 1500. I select South and expect 1200. Finally, I clear the selection and return to 2700. These three deliberate states tell me more than random clicks across a production report containing thousands of records.

Order region is a better title than Region when the company also uses customer region or account-manager region. The report reader should not need to know the model’s field names. Translating those names into the language of the decision is part of the author’s job. If two fields have different meanings, I expand both titles, even when shorter labels would look more elegant.

Check the intersection of multiple selections

Next I add a category slicer. North together with Services should leave order C and an amount of 200. Clearing the region while keeping Services should produce 500. I want to reconstruct the report state from the visible choices and identify the corresponding rows.

A combination with no data is also useful. As an extension, add a category found in only one region, then select the other region. Decide what the reader should understand from the result. A missing bar alone cannot explain whether no orders qualify or whether the report has stopped working.

I avoid mechanically replacing every missing result with zero. Zero makes a specific statement and needs to match the metric’s definition. During design, I write down the intended empty-state behaviour and agree it with the reader. This matters when comparing a completed period with another period whose data has not yet arrived. The same blank space can otherwise conceal two very different situations.

Make exceptions to interactions understandable

Sometimes an overall card should remain a reference while the reader selects a region. I inspect the slicer’s interaction with that card and disable it for that object when this is the intended design. Other visuals can continue responding to the selection.

I label the reference card All regions and the other card Selected region. With North selected, they show 2700 and 1500 respectively. The reader should understand the difference without opening the report editor. Giving both cards the same title would create an unnecessary argument about which number is correct.

Disabling one slicer’s interaction does not automatically remove every other restriction. I check page and report filters separately. My design note uses a complete sentence: this card ignores the region choice in this slicer and retains the shared period. That is much more precise than calling it an unfiltered card. The note also gives me something concrete to revisit after a later change.

Trace the filter path when a slicer seems ineffective

Once fields move into separate tables, the model becomes part of the investigation. A selection in a region table needs an appropriate relationship path to reach orders. Microsoft explains filter propagation through relationships, including the effects of relationship direction and active status.

I compare a simple diagnostic table with the card, identify the table supplying the slicer field and locate the table supplying the amount. I then inspect the relationship and keys before examining the measure definition. If the slicer field comes from a disconnected table, changing its colour cannot create a missing path.

I resist enabling bidirectional filtering everywhere as an automatic repair. I want to draw the intended route from a selected region to its orders and explain why the route is needed. I describe the purpose of the relationship before trying to repair a complex calculation. First, I make an ordinary sum work with a tiny dataset.

Keep filter visibility separate from data access

Hiding or locking a filter can organise the interface. I do not treat it as an access-control mechanism. Restricting what a person may read requires appropriate permissions and model security, such as correctly configured row-level security, with role validation.

For a beginner, the useful boundary is straightforward: hiding the region control does not establish that other regions are inaccessible. A hidden page or an unobtrusively named filter likewise cannot demonstrate that the reader is prevented from obtaining data through other available routes.

I also keep material analytical restrictions visible. A card excluding returns needs a definition or title that communicates that exclusion. Someone may compare it with a finance report that includes returns. A tidy screen with no visible filtering controls still needs to explain why those two numbers differ. Presentation choices should make that explanation easier to find.

Choose controls around real questions

I select the most frequent decisions instead of imposing a universal slicer count. A regional manager may need period and category. Someone comparing regional performance may need all regions visible together. They are different tasks, even though they use the same order table.

For each control, I write a short purpose. Region narrows responsibility, period sets comparable time, and category describes the mix of orders. If another control supports no identifiable question, I leave it aside. A report does not have to expose every field in its semantic model.

I also examine the page at a smaller width. An expanded list of dozens of values may obscure the result, while shortened labels may stop distinguishing categories. I then consider a different selection style, a clearer list or a separate detail page. Whether the reader can complete the task matters more than how many controls fit in one row.

Give the reader a route back to the starting state

The reader needs to recognise the starting state and know how to return to it. I show where to clear a selection and ask them to do it themselves. Then I ask which region and period are currently active. If the answer requires guessing, I improve how the state is communicated.

For acceptance, I describe three tasks: find North’s amount, compare categories in South, and return to the overall view. I observe whether the user understands each changing number. I avoid immediately pointing at the right button. That brief pause often reveals an unclear title or an interaction exception which seemed obvious during development.

After changing a model, measure or layout, I repeat the same tasks. Written reference figures make differences visible before the report reaches a meeting. A successful interaction still has to serve the question agreed at the beginning. Smooth animation alone provides little evidence of that.

Check whether an extra detail changes the question

A category table and an order list have different grains. Adding Order ID to a category table breaks a category into its individual orders. Before making that layout change, I ask whether the visual still answers the same question, especially when a filter applies to an aggregated result.

Equipment exceeds 1000 as a category even though none of its individual orders does. A request to add order numbers therefore needs clarification within the report requirements: should the condition still select categories, or should it select individual orders? I establish the expected list before choosing a detail presentation.

I keep a simple diagnostic view with identifier, region, category and amount. It need not be the report’s main page. Its purpose is to explain a displayed number through specific source records, using the five-row example before scaling the design.

Write down complete states before changing the layout

I describe one reference state: North selected, all categories, selected-region card 1500 and all-regions card 2700. Then I add North with Services. The selected-region result becomes 200, but the comparison card needs a more explicit definition.

If the comparison ignores region but keeps category, it should show 500 for Services. If it ignores both slicers, it should retain 2700. Those are materially different report designs. The required comparison determines which is appropriate.

I record the route back to the initial state and the shared period too. Another person can then repeat the inspection without remembering our discussion. After moving or renaming controls, I revisit those states. Correct numbers still need a layout which makes their meaning recognisable.

Exercise: explain every number from the current state

Enter the five synthetic orders and build an amount card, a category table and a region slicer. Write down the expected 2700, 1500 and 1200 before selecting anything. Then add category and explain why North with Services gives 200. Identify the individual orders before moving on.

Prepare a separate category visual filtered to a sum above 1000. Compare it with a version filtering individual Amount values above 1000. Explain why the first keeps Equipment and the second has no qualifying orders. This checks your understanding of the condition rather than your memory of button names.

Finally, disable the region slicer’s effect on one card, label the card accurately and ask someone else to read it. Can they describe the selection, the scope of the card and the way back to the overall view? That gives you a useful basis for a readable report. When preparing its next data source, continue with merging queries in Power Query.

Key takeaways

Do Power BI filters always operate on individual rows?

No. A visual-level measure filter can restrict categories according to their aggregated result. Distinguish an individual order amount from the sum of order amounts.

Why does one card ignore my slicer?

I check the interaction with the card, model relationships and the measure definition. A deliberate exception should be explained in the card title.

Does hiding a filter secure regional data?

Hiding a filter organises the interface. Data access requires appropriate permissions and model security, such as correctly configured row-level security.

AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.

Kajo Rudziński

Newsletter KajoData

Let’s keep in touch.

I write about data analysis, learning and changing careers. My newsletter and the sign-up form are in Polish. If that works for you, I’d be glad to see you there.