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.

Practice

1. What does the CALCULATE function do when you add multiple filters inside it in Power BI?

easy

A. It applies all filters together using AND logic to narrow down the data.

B. It applies only the first filter and ignores the rest.

C. It applies filters using OR logic, showing data matching any filter.

D. It removes all filters and shows the full dataset.

Solution

1. Step 1: Understand CALCULATE with filters

   CALCULATE changes the filter context by applying filters inside it.

2. Step 2: Analyze multiple filters behavior

   Multiple filters inside CALCULATE combine with AND logic, so all must be true.

3. Final Answer:

   It applies all filters together using AND logic to narrow down the data. -> Option A

4. Quick Check:

   CALCULATE with multiple filters = AND logic [OK]

Hint: Remember: multiple filters in CALCULATE use AND logic [OK]

Common Mistakes:

• Thinking filters use OR logic inside CALCULATE
• Assuming only one filter applies
• Believing CALCULATE removes filters

2. Which of the following is the correct syntax to use CALCULATE with two filters on columns Category and Year?

easy

A. CALCULATE(SUM(Sales[Amount]), Sales[Category] = "Books", Sales[Year] = 2023)

B. CALCULATE(SUM(Sales[Amount]), FILTER(Sales[Category] = "Books"), FILTER(Sales[Year] = 2023))

C. CALCULATE(SUM(Sales[Amount]), Sales[Category] == "Books" && Sales[Year] == 2023)

D. CALCULATE(SUM(Sales[Amount]), Sales[Category] = "Books" OR Sales[Year] = 2023)

Solution

1. Step 1: Check filter syntax inside CALCULATE

   Filters inside CALCULATE are written as column = value pairs separated by commas.

2. Step 2: Validate each option's syntax

   CALCULATE(SUM(Sales[Amount]), Sales[Category] = "Books", Sales[Year] = 2023) uses correct syntax with commas and single equals; others use wrong operators or FILTER incorrectly.

3. Final Answer:

   CALCULATE(SUM(Sales[Amount]), Sales[Category] = "Books", Sales[Year] = 2023) -> Option A

4. Quick Check:

   Correct filter syntax = column = value, separated by commas [OK]

Hint: Use commas and single equals for filters inside CALCULATE [OK]

Common Mistakes:

• Using double equals (==) instead of single equals (=)
• Using OR instead of commas between filters
• Wrapping filters incorrectly with FILTER function

3. Given the following DAX measure:

SalesFiltered = CALCULATE(SUM(Sales[Amount]), Sales[Region] = "West", Sales[Product] = "Laptop")

If the Sales table has:
- Region: ["West", "East", "West"]
- Product: ["Laptop", "Laptop", "Tablet"]
- Amount: [100, 200, 300]
What is the result of SalesFiltered?

medium

A. 200

B. 300

C. 100

D. 400

Solution

1. Step 1: Identify rows matching both filters

   Filters: Region = "West" AND Product = "Laptop". Rows: 1 (West, Laptop, 100), 2 (East, Laptop, 200), 3 (West, Tablet, 300). Only row 1 matches both.

2. Step 2: Sum Amount for matching rows

   Only row 1 matches, Amount = 100.

3. Final Answer:

   100 -> Option C

4. Quick Check:

   Sum filtered rows = 100 [OK]

Hint: Filters combine with AND, so sum only matching rows [OK]

Common Mistakes:

• Summing rows matching only one filter
• Adding all amounts regardless of filters
• Confusing OR with AND logic

4. You wrote this DAX measure:

FilteredSales = CALCULATE(SUM(Sales[Amount]), Sales[Category] = "Electronics" AND Sales[Year] = 2022)

But it gives an error. What is wrong?

medium

A. Column names cannot be used as filters.

B. SUM cannot be used inside CALCULATE.

C. Filters must be wrapped inside FILTER function.

D. Using AND inside CALCULATE filters is invalid syntax.

Solution

1. Step 1: Check filter syntax inside CALCULATE

   Filters inside CALCULATE are separated by commas, not combined with AND.

2. Step 2: Identify correct way to apply multiple filters

   Use commas between filters: Sales[Category] = "Electronics", Sales[Year] = 2022.

3. Final Answer:

   Using AND inside CALCULATE filters is invalid syntax. -> Option D

4. Quick Check:

   Filters separated by commas, not AND [OK]

Hint: Separate filters with commas, not AND inside CALCULATE [OK]

Common Mistakes:

• Using AND or OR inside CALCULATE filters
• Wrapping filters unnecessarily with FILTER
• Misplacing parentheses

5. You want to calculate total sales for products in the "Furniture" category sold in 2023, but exclude sales from the "East" region. Which DAX measure correctly uses CALCULATE with multiple filters to achieve this?

hard

A. CALCULATE(SUM(Sales[Amount]), Sales[Category] = "Furniture" OR Sales[Year] = 2023, Sales[Region] <> "East")

B. CALCULATE(SUM(Sales[Amount]), Sales[Category] = "Furniture", Sales[Year] = 2023, Sales[Region] <> "East")

C. CALCULATE(SUM(Sales[Amount]), FILTER(Sales[Category] = "Furniture" && Sales[Year] = 2023 && Sales[Region] <> "East"))

D. CALCULATE(SUM(Sales[Amount]), Sales[Category] = "Furniture", Sales[Year] = 2023, NOT Sales[Region] = "East")

Solution

1. Step 1: Understand filter requirements

   We want Category = "Furniture", Year = 2023, and Region not equal to "East".

2. Step 2: Check filter syntax for exclusion

   Use Sales[Region] <> "East" to exclude East region. Filters inside CALCULATE combine with AND.

3. Step 3: Validate options

   CALCULATE(SUM(Sales[Amount]), Sales[Category] = "Furniture", Sales[Year] = 2023, Sales[Region] <> "East") uses correct syntax and logic. CALCULATE(SUM(Sales[Amount]), Sales[Category] = "Furniture" OR Sales[Year] = 2023, Sales[Region] <> "East") uses OR incorrectly. CALCULATE(SUM(Sales[Amount]), FILTER(Sales[Category] = "Furniture" && Sales[Year] = 2023 && Sales[Region] <> "East")) has invalid FILTER syntax (missing table). CALCULATE(SUM(Sales[Amount]), Sales[Category] = "Furniture", Sales[Year] = 2023, NOT Sales[Region] = "East") uses NOT incorrectly.

4. Final Answer:

   CALCULATE(SUM(Sales[Amount]), Sales[Category] = "Furniture", Sales[Year] = 2023, Sales[Region] <> "East") -> Option B

5. Quick Check:

   Use commas and <> for exclusion in CALCULATE filters [OK]

Hint: Use <> for NOT EQUAL and commas for multiple filters [OK]

Common Mistakes:

• Using OR instead of AND logic
• Using NOT keyword incorrectly
• Overcomplicating with FILTER when not needed