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.