Filter context concept in Power BI - Step-by-Step Guide

Introduction

Filter context controls which data is included when Power BI calculates a value. It helps show results for specific parts of your data, like sales for one region or one product category.

When you want to see total sales only for the current year on a report.

When your dashboard needs to show revenue by each product category separately.

When you want to compare monthly sales but only for a selected region.

When you create a measure that should change based on slicer selections.

When you want to filter data dynamically without changing the original dataset.

Steps

Step 1: Open your Power BI report

- Power BI Desktop main window

You see your report with visuals and data loaded

Step 2: Add a slicer visual

- Visualizations pane

A slicer appears on the report canvas

💡 Use slicers to create interactive filters for your report

Step 3: Drag a field like 'Region' into the slicer

- Fields pane to slicer visual

Slicer shows all regions as selectable options

Step 4: Click a region in the slicer

- Slicer visual on report canvas

All other visuals update to show data only for the selected region

Step 5: Create a measure using DAX that sums sales

- Modeling tab > New measure

A new measure appears in the Fields pane

💡 Example measure: Total Sales = SUM(Sales[Amount])

Step 6: Add a card visual and drag the measure to it

- Visualizations pane and Fields pane

Card shows total sales filtered by the slicer selection

Before vs After

Before

Report shows total sales for all regions combined: $1,000,000

After

After selecting 'West' region in slicer, visuals show sales only for West: $250,000

Settings Reference

Slicer selection

📍 Slicer visual on report canvas

Controls how users filter data by choosing one or more values

Default: Multi-select enabled

Visual interactions

📍 Format tab > Edit interactions

Controls how slicers and filters affect other visuals

Default: Filter

Common Mistakes

Expecting a measure to ignore slicer filters automatically

Measures respect filter context by default, so slicer selections affect results

Use DAX functions like ALL() to remove filter context if needed

Not adding fields to slicers or filters to create filter context

Without slicers or filters, filter context does not change and results stay the same

Add slicers or page/report filters to control filter context interactively

Summary

Filter context controls which data is included when Power BI calculates values.

It changes dynamically based on slicers, filters, and visual selections.

Measures respect filter context unless explicitly told not to.

Practice

1. What does filter context control in Power BI calculations?

easy

A. The color scheme of the report visuals

B. The size of the data model

C. Which data rows are included in the calculation

D. The refresh schedule of the dataset

Solution

1. Step 1: Understand filter context meaning

   Filter context determines which rows of data are considered when a measure or calculation runs.

2. Step 2: Relate filter context to calculation

   It controls the subset of data used, affecting the calculation result.

3. Final Answer:

   Which data rows are included in the calculation -> Option C

4. Quick Check:

   Filter context = data rows included [OK]

Hint: Filter context decides which rows affect your measure [OK]

Common Mistakes:

• Confusing filter context with visual formatting
• Thinking filter context controls data refresh
• Mixing filter context with data model size

2. Which of the following is the correct syntax to change filter context using CALCULATE in DAX?

easy

A. CALCULATE(SUM(Sales[Amount]), Sales[Region] = "West")

B. CALCULATE(SUM(Sales[Amount]) FILTER Sales[Region] = "West")

C. CALCULATE SUM(Sales[Amount]) WHERE Sales[Region] = "West"

D. CALCULATE(SUM(Sales[Amount]), FILTER Sales[Region] == "West")

Solution

1. Step 1: Recall CALCULATE syntax

   CALCULATE takes an expression and one or more filter conditions separated by commas.

2. Step 2: Check each option's syntax

   CALCULATE(SUM(Sales[Amount]), Sales[Region] = "West") uses correct syntax: CALCULATE(expression, filter). Others misuse FILTER keyword or operators.

3. Final Answer:

   CALCULATE(SUM(Sales[Amount]), Sales[Region] = "West") -> Option A

4. Quick Check:

   CALCULATE(expression, filter) correct syntax [OK]

Hint: Use comma to separate filters inside CALCULATE [OK]

Common Mistakes:

• Using FILTER keyword incorrectly inside CALCULATE
• Using WHERE instead of filter arguments
• Using double equals (==) instead of single equals (=) in filters

3. Given this measure:
Total West Sales = CALCULATE(SUM(Sales[Amount]), Sales[Region] = "West")
What is the output if Sales table has:
- Region: East, Amount: 100
- Region: West, Amount: 200
- Region: West, Amount: 300
?

medium

A. 600

B. 500

C. 300

D. 100

Solution

1. Step 1: Identify rows matching filter

   Filter is Sales[Region] = "West", so rows with amounts 200 and 300 match.

2. Step 2: Sum amounts of filtered rows

   Sum = 200 + 300 = 500.

3. Final Answer:

   500 -> Option B

4. Quick Check:

   Sum of West region sales = 500 [OK]

Hint: Sum only rows where Region = West [OK]

Common Mistakes:

• Adding all rows ignoring filter
• Adding only one West row
• Confusing East and West regions

4. You wrote this measure:
West Sales = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Region] = "West"))
But it returns the total sales for all regions. What is the likely problem?

medium

A. FILTER function is missing a table argument

B. The filter condition inside FILTER is incorrect

C. CALCULATE ignores FILTER and uses all rows by default

D. The FILTER function returns a table but CALCULATE expects a boolean filter

Solution

1. Step 1: Understand FILTER inside CALCULATE

   FILTER returns a table of rows matching condition, which is valid inside CALCULATE.

2. Step 2: Check why all rows are included

   If measure returns all sales, likely the filter is ignored or overridden by outer filter context.

3. Step 3: Identify common mistake

   Sometimes users expect FILTER to act like a boolean filter, but it returns a table. If misused, CALCULATE may ignore it.

4. Final Answer:

   The FILTER function returns a table but CALCULATE expects a boolean filter -> Option D

5. Quick Check:

   FILTER returns table, CALCULATE needs filter context [OK]

Hint: FILTER returns table; CALCULATE needs filter context [OK]

Common Mistakes:

• Thinking FILTER returns a boolean value
• Misplacing FILTER arguments
• Assuming CALCULATE ignores FILTER always

5. You want to create a measure that sums sales only for the current year selected in a slicer, but also always filters Region = "East" regardless of slicer. Which DAX expression correctly applies this filter context?

hard

A. CALCULATE(SUM(Sales[Amount]), Sales[Region] = "East")

B. CALCULATE(SUM(Sales[Amount]), ALL(Sales[Year]), Sales[Region] = "East")

C. CALCULATE(SUM(Sales[Amount]), Sales[Region] = "East", REMOVEFILTERS(Sales[Year]))

D. CALCULATE(SUM(Sales[Amount]), FILTER(ALL(Sales), Sales[Region] = "East"))

Solution

1. Step 1: Understand slicer effect on filter context

   Slicer on Year creates filter context for current year automatically.

2. Step 2: Add fixed filter for Region

   Adding Sales[Region] = "East" inside CALCULATE adds this filter on top of slicer filters.

3. Step 3: Check options for preserving slicer

   CALCULATE(SUM(Sales[Amount]), Sales[Region] = "East") keeps slicer filter on Year and adds Region filter. CALCULATE(SUM(Sales[Amount]), ALL(Sales[Year]), Sales[Region] = "East") removes Year filter with ALL, which is wrong. CALCULATE(SUM(Sales[Amount]), Sales[Region] = "East", REMOVEFILTERS(Sales[Year])) removes Year filter with REMOVEFILTERS, which is wrong. CALCULATE(SUM(Sales[Amount]), FILTER(ALL(Sales), Sales[Region] = "East")) removes all filters with ALL, losing slicer effect.

4. Final Answer:

   CALCULATE(SUM(Sales[Amount]), Sales[Region] = "East") -> Option A

5. Quick Check:

   CALCULATE adds Region filter, keeps slicer Year filter [OK]

Hint: Add fixed filter in CALCULATE, slicer filters stay [OK]

Common Mistakes:

• Using ALL removes slicer filters unintentionally
• Using REMOVEFILTERS removes slicer filters unintentionally
• Overriding slicer filters when not needed