Filter context concept in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to understand how sales vary by product category and region, and how filters affect the sales calculations.

📊 Data: You have sales data including Date, Region, Product Category, and Sales Amount.

🎯 Deliverable: Create a Power BI report that shows total sales by product category and region, demonstrating how filter context changes the sales totals.

Sample Data

Date	Region	Product Category	Sales Amount
2024-01-05	North	Electronics	1000
2024-01-07	South	Electronics	1500
2024-01-10	North	Clothing	700
2024-01-12	East	Clothing	900
2024-01-15	South	Furniture	1200
2024-01-18	East	Furniture	1100
2024-01-20	North	Electronics	1300
2024-01-22	South	Clothing	800
2024-01-25	East	Electronics	1400
2024-01-28	North	Furniture	1000

1

Step 1: Load the sales data into Power BI and create a table visual with columns: Region, Product Category, and Sales Amount.

No formula needed; just load data and create table visual.

Expected Result

Table shows all sales records with their regions, product categories, and sales amounts.

2

Step 2: Create a measure to calculate total sales using DAX.

Total Sales = SUM('Sales'[Sales Amount])

Expected Result

Measure 'Total Sales' sums all sales amounts in the data.

3

Step 3: Add a matrix visual with Rows = Product Category, Columns = Region, Values = Total Sales measure.

Configure matrix visual fields accordingly.

Expected Result

Matrix shows total sales broken down by product category and region.

4

Step 4: Add slicers for Region and Product Category to the report page.

Add slicers using Region and Product Category columns.

Expected Result

User can filter the report by selecting specific regions or product categories.

5

Step 5: Explain filter context: When a user selects a region or product category in slicers, the Total Sales measure recalculates only for the filtered data.

No formula; explanation of filter context behavior.

Expected Result

Total Sales values update dynamically based on slicer selections, showing filtered sales totals.

6

Step 6: Create a measure to calculate total sales ignoring filters on Region.

Total Sales All Regions = CALCULATE([Total Sales], ALL('Sales'[Region]))

Expected Result

Measure shows total sales ignoring any Region filter but respects other filters.

7

Step 7: Add the 'Total Sales All Regions' measure to the matrix visual to compare filtered and unfiltered sales by region.

Add measure to matrix values.

Expected Result

Matrix shows two columns per region: filtered sales and sales ignoring region filter.

Final Result

Product Category | North  | South  | East   
-------------------------------------------
Electronics      | 2300   | 1500   | 1400   
Clothing         | 700    | 800    | 900    
Furniture        | 1000   | 1200   | 1100   

Filters Applied: Region = South

Total Sales updates to show only South region sales.

Additional Column: Total Sales All Regions shows sales ignoring Region filter.

✓Sales vary by product category and region, with Electronics having highest sales in North region.

✓Filter context changes the sales totals dynamically when slicers are used.

✓Using ALL function in DAX removes filter on Region, showing total sales across all regions regardless of slicer selection.

Bonus Challenge

Create a measure that calculates total sales only for the selected product category but ignores any region filter.

Show Hint

Use CALCULATE with ALL on Region column and keep the filter on Product Category from filter context.

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