Filter context concept in Power BI - Dashboard Guide

Dashboard Mode - Filter context concept

Business Question

How do sales change when we select different product categories and regions? This dashboard helps us see sales totals filtered by category and region.

Sample Data

OrderID	ProductCategory	Region	SalesAmount
1001	Electronics	East	200
1002	Clothing	West	150
1003	Electronics	West	300
1004	Furniture	East	400
1005	Clothing	East	100
1006	Furniture	West	250
1007	Electronics	East	350

Dashboard Components

• Filter Slicer: Product Category
  Allows selecting one or more product categories to filter data.
• Filter Slicer: Region
  Allows selecting one or more regions to filter data.
• KPI Card: Total Sales
  Measure formula:
  Total Sales = SUM(Sales[SalesAmount])
  Shows total sales amount based on selected filters.
• Bar Chart: Sales by Product Category
  Shows sales totals for each product category, filtered by region slicer.
• Table: Detailed Sales
  Shows OrderID, ProductCategory, Region, and SalesAmount filtered by slicers.

Dashboard Layout

+----------------------+----------------------+
| Product Category     | Region               |
| Slicer               | Slicer               |
+----------------------+----------------------+
|                      Total Sales KPI         |
|                                              |
+----------------------------------------------+
| Bar Chart: Sales by Product Category          |
|                                              |
+----------------------------------------------+
| Table: Detailed Sales                          |
|                                              |
+----------------------------------------------+
  

Interactivity

When you select a product category in the Product Category slicer, the Total Sales KPI, Bar Chart, and Detailed Sales Table update to show only sales for that category.

When you select a region in the Region slicer, all visuals update to show sales only for that region.

Both slicers work together to filter the data. For example, selecting "Electronics" and "East" shows sales only for Electronics in the East region.

Self Check

If you add a filter selecting Product Category = Furniture and Region = West, which sales orders appear in the detailed sales table?

Answer: Only OrderID 1006 with Furniture in West region and SalesAmount 250 will appear.

Key Result

Dashboard showing total sales filtered by product category and region using slicers to demonstrate 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