Filter context concept in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the DAX formula to calculate total sales filtered by product category.

Power BI

Total Sales = CALCULATE(SUM(Sales[Amount]), Sales[Category] = [1])

Drag options to blanks, or click blank then click option'

A"Bikes"

BFILTER

CSUM(Sales[Amount])

DBikes

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to put quotes around text values.

Using a function name instead of a value.

In DAX, text values must be enclosed in double quotes. So to filter by category 'Bikes', use "Bikes".

2fill in blank

medium

Complete the DAX formula to calculate sales only for the year 2023.

Power BI

Sales 2023 = CALCULATE(SUM(Sales[Amount]), Sales[Year] [1] 2023)

Drag options to blanks, or click blank then click option'

A=

B==

C>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using double '==' instead of '=' for comparison.

Using greater or less than operators by mistake.

In DAX, the equality operator is '=', not double equals '=='.

3fill in blank

hard

Fix the error in this DAX formula to correctly filter sales for the 'East' region.

Power BI

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

Drag options to blanks, or click blank then click option'

A==

B===

C=

D=>

Attempts:

3 left

💡 Hint

Common Mistakes

Using double equals '==' or other invalid operators.

Using invalid operators like '===' or '=>.'

DAX uses '=' for equality comparison, not double equals '==' or other operators.

4fill in blank

hard

Fill both blanks to create a measure that calculates total sales for products priced above 100.

Power BI

High Price Sales = CALCULATE(SUM(Sales[Amount]), Sales[Price] [1] [2])

Drag options to blanks, or click blank then click option'

A>

B100

C==

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '==' instead of '>' for comparison.

Using '<' which filters prices less than 100.

The filter condition should check if Price is greater than 100, so use '>' and '100'.

5fill in blank

hard

Fill all three blanks to create a measure that calculates total sales for the 'West' region in 2022.

Power BI

West 2022 Sales = CALCULATE(SUM(Sales[Amount]), Sales[Region] [1] [2], Sales[Year] [3] 2022)

Drag options to blanks, or click blank then click option'

A=

B"West"

D"East"

Attempts:

3 left

💡 Hint

Common Mistakes

Using invalid operators like '==' instead of '='.

Not quoting text values like "West".

Mixing up regions or years.

Use '=' for equality checks and put text values in quotes. So Region = "West" and Year = 2022.

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