CALCULATE with multiple filters in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Total Sales with Multiple Filters

Given a sales table with columns SalesAmount, Region, and ProductCategory, what is the result of this DAX measure?

Measure = CALCULATE(SUM(Sales[SalesAmount]), Sales[Region] = "West", Sales[ProductCategory] = "Electronics")

Assume the following data:

• West, Electronics: 1000
• West, Furniture: 500
• East, Electronics: 700

What is the value of Measure?

Power BI

Measure = CALCULATE(SUM(Sales[SalesAmount]), Sales[Region] = "West", Sales[ProductCategory] = "Electronics")

A 1000

B 1500

C 700

D 2200

💡 Hint

Remember CALCULATE applies all filters together.

The measure sums SalesAmount only where Region is West and ProductCategory is Electronics. Only that row has 1000.

❓ visualization

intermediate

Visualizing Sales by Region and Category with Filters

You want to create a bar chart showing total sales for Electronics products only, across all regions. Which filter should you apply in the visualization?

A Filter the chart to Region = "Electronics"

B Filter the chart to SalesAmount > 1000

C Filter the chart to ProductCategory = "Electronics"

D Filter the chart to ProductCategory = "Furniture"

💡 Hint

Think about which column holds product categories.

Filtering on ProductCategory = "Electronics" shows sales only for electronics products.

❓ data_modeling

advanced

Combining Multiple Filters in CALCULATE with Relationships

You have two tables: Sales with columns SalesAmount, ProductID, and CustomerID, and Products with columns ProductID and Category. There is a relationship between Sales[ProductID] and Products[ProductID].

Which DAX measure correctly calculates total sales for products in the "Electronics" category and customers in the "West" region (region is in Sales table)?

A CALCULATE(SUM(Sales[SalesAmount]), Sales[Category] = "Electronics", Sales[Region] = "West")

B CALCULATE(SUM(Sales[SalesAmount]), Products[Category] = "Electronics", Sales[Region] = "West")

C CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, Sales[Category] = "Electronics" && Sales[Region] = "West"))

D CALCULATE(SUM(Sales[SalesAmount]), FILTER(Products, Products[Category] = "Electronics"), Sales[Region] = "West")

💡 Hint

Remember to use FILTER when filtering columns from related tables.

Option D uses FILTER on Products table to filter by Category, combined with Sales region filter. Option D tries direct filter on related table column which doesn't work. Options C and D use non-existent columns.

🔧 Formula Fix

advanced

Identify the Error in CALCULATE with Multiple Filters

What error will this DAX expression cause?

Measure = CALCULATE(SUM(Sales[SalesAmount]), Sales[Region] = "West" && Sales[ProductCategory] = "Electronics")

Power BI

Measure = CALCULATE(SUM(Sales[SalesAmount]), Sales[Region] = "West" && Sales[ProductCategory] = "Electronics")

A Runtime error due to ambiguous filter context

B Syntax error because multiple filters must be separated by commas, not combined with &&

C Returns total sales ignoring filters

D No error, returns correct filtered sum

💡 Hint

Check how multiple filters are passed to CALCULATE.

CALCULATE expects multiple filter arguments separated by commas. Using && inside a single filter argument causes syntax error.

🧠 Conceptual

expert

Understanding Filter Context in CALCULATE with Multiple Filters

Which statement best describes how CALCULATE applies multiple filter arguments?

A CALCULATE applies all filters together as an AND condition, modifying the filter context accordingly

B CALCULATE applies filters one by one, overwriting previous filters each time

C CALCULATE applies filters as OR conditions, including rows matching any filter

D CALCULATE ignores all filters except the first one

💡 Hint

Think about how multiple filters combine logically.

CALCULATE combines multiple filter arguments as AND conditions, so only rows meeting all filters are included.