CALCULATE function introduction in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Total Sales for a Specific Year

Given a Sales table with columns [SalesAmount] and [Year], what is the result of this DAX measure?

TotalSales2019 = CALCULATE(SUM(Sales[SalesAmount]), Sales[Year] = 2019)

If the Sales table has these rows:
- Year: 2018, SalesAmount: 100
- Year: 2019, SalesAmount: 200
- Year: 2019, SalesAmount: 300
- Year: 2020, SalesAmount: 400

What is the value of TotalSales2019?

Power BI

TotalSales2019 = CALCULATE(SUM(Sales[SalesAmount]), Sales[Year] = 2019)

A 500

B 1000

C 400

D 300

💡 Hint

Think about filtering only the rows where Year is 2019 before summing.

CALCULATE changes the filter context to only include rows where Year equals 2019. Then it sums SalesAmount for those rows: 200 + 300 = 500.

❓ visualization

intermediate

Visualizing Sales with CALCULATE Filter

You want to create a bar chart showing total sales only for the year 2020 using a measure with CALCULATE.

Which of these DAX measures will correctly show total sales for 2020 in the bar chart?

A TotalSales2020 = CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, Sales[Year] <> 2020))

B TotalSales2020 = CALCULATE(SUM(Sales[SalesAmount]), Sales[Year] = 2020)

C TotalSales2020 = SUM(Sales[SalesAmount]) + Sales[Year] = 2020

D TotalSales2020 = CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales[Year]))

💡 Hint

Remember CALCULATE changes the filter context to what you specify.

Option B filters Sales to Year = 2020 and sums SalesAmount. Option B is invalid syntax. Option B filters out 2020, so it excludes those sales. Option B removes all Year filters, showing total sales for all years.

🧠 Conceptual

advanced

Understanding CALCULATE Filter Context Modification

Which statement best describes what the CALCULATE function does in DAX?

A It creates a new table with all rows from the original table.

B It only sums numeric columns without any filtering.

C It changes the filter context for the calculation by applying specified filters.

D It removes all filters from the current report page.

💡 Hint

Think about how CALCULATE affects what data is included in a measure.

CALCULATE modifies the filter context by applying the filters you specify, then evaluates the expression in that new context.

🔧 Formula Fix

advanced

Identify the Error in CALCULATE Usage

What error will this DAX measure cause?

Measure = CALCULATE(SUM(Sales[Amount]), Sales[Year] == 2021)

Power BI

Measure = CALCULATE(SUM(Sales[Amount]), Sales[Year] == 2021)

A Syntax error due to double equals (==) instead of single equals (=)

B No error, measure returns sum for year 2021

C Type error because SUM cannot be used inside CALCULATE

D Runtime error because Sales[Year] column does not exist

💡 Hint

Check the filter condition syntax inside CALCULATE.

DAX uses a single equals sign (=) for filter conditions. Using double equals (==) causes a syntax error.

🎯 Scenario

expert

Using CALCULATE to Override Existing Filters

You have a report page filtered to Year = 2022. You want a measure that shows total sales for Year = 2021 regardless of the page filter.

Which DAX measure achieves this?

A TotalSales2021 = CALCULATE(SUM(Sales[SalesAmount]), Sales[Year] = 2021)

B TotalSales2021 = CALCULATE(SUM(Sales[SalesAmount]), REMOVEFILTERS(Sales[Year]))

C TotalSales2021 = SUM(Sales[SalesAmount])

D TotalSales2021 = CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales[Year]), Sales[Year] = 2021)

💡 Hint

Think about how to ignore existing filters on Year before applying your own filter.

Option D removes all filters on Sales[Year] first, then applies the filter Year = 2021, ignoring the page filter Year = 2022. Option D applies the filter but does not remove existing filters, so the page filter still applies. Option D ignores filtering. Option D removes filters but does not reapply the Year = 2021 filter.