Filter context concept in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Filter Context in DAX Measures

In Power BI, what does the filter context affect when calculating a measure?

A It changes the data type of the columns used in the measure.

B It determines which rows of data are included in the calculation of the measure.

C It modifies the visual layout of the report page.

D It controls the refresh frequency of the dataset.

💡 Hint

Think about how filters affect what data is used in calculations.

Filter context controls which rows are considered when a measure is calculated. It filters the data before aggregation.

❓ dax_lod_result

intermediate

Result of a Measure with Filter Context

Given the Sales table with columns: Product, Region, and SalesAmount, and the measure:

Total Sales = SUM(Sales[SalesAmount])

If a report visual filters Region to 'West', what is the output of Total Sales?

A Sum of SalesAmount only for the 'West' region.

B Average SalesAmount for all regions.

C Count of rows in Sales table.

D Sum of SalesAmount for all regions.

💡 Hint

Filter context limits data to the selected region.

The filter on Region='West' limits the rows considered, so the measure sums only those rows.

🔧 Formula Fix

advanced

Why Does This Measure Ignore Report Filters?

Consider this DAX measure:

All Sales = CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales))

Why does this measure show the total sales ignoring any report filters?

A Because CALCULATE disables all filters by default.

B Because SUM ignores filters applied in visuals.

C Because the measure is not connected to the data model.

D Because the ALL function removes all filters from the Sales table, ignoring filter context.

💡 Hint

Think about what the ALL function does to filters.

ALL removes filters on the Sales table, so CALCULATE sums all rows regardless of filters.

❓ visualization

advanced

Best Visualization to Show Filter Context Impact

You want to show how sales differ by Region and Product Category, reflecting filter context changes dynamically. Which visualization type best supports this?

A Matrix visual with Region as rows and Product Category as columns showing sales measures.

B Card visual showing overall total sales only.

C Pie chart showing total sales without any breakdown.

D Text box with static sales description.

💡 Hint

Choose a visual that can show multiple categories and respond to filters.

A matrix visual can display sales by Region and Product Category, reflecting filter context dynamically.

🎯 Scenario

expert

Calculating Sales Growth Ignoring Certain Filters

You want to create a measure that calculates sales growth compared to the previous year but ignores any filters on Product Category while respecting other filters like Region and Date. Which DAX expression achieves this?

A Sales Growth = CALCULATE(SUM(Sales[SalesAmount]), FILTER(ALL(Sales), Sales[Product Category] = EARLIER(Sales[Product Category]))) - SUM(Sales[SalesAmount])

B Sales Growth = SUM(Sales[SalesAmount]) - CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSYEAR(Date[Date]))

C Sales Growth = CALCULATE(SUM(Sales[SalesAmount]), SAMEPERIODLASTYEAR(Date[Date]), ALL(Sales[Product Category])) - SUM(Sales[SalesAmount])

D Sales Growth = CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales)) - SUM(Sales[SalesAmount])

💡 Hint

Use ALL to remove filters on Product Category only.

Option C removes filters on Product Category only, keeping other filters intact, and compares sales to previous year.

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