Power BI Intermediate Lesson 1: CALCULATE and Filter Context

📎 This article includes 3 downloadable practice files ↓

📘 Power BI Intermediate Course · Lesson 1 of 10

Every number in a Power BI visual is a measure evaluated under a filter context: the row, column, slicer and page filters that apply to that cell. CALCULATE is the only function that changes that context.

Total Sales      = SUM ( Sales[Amount] )
Computers Sales  = CALCULATE ( [Total Sales], Products[Category] = "Computers" )
North FY26 Sales = CALCULATE ( [Total Sales], Customers[Region] = "North", 'Date'[FY] = "FY 2025-26" )

In the practice model: Total Sales ₹2,80,94,576; Computers ₹1,43,28,920; North in FY 2025-26 ₹58,10,689.

Replace vs keep

A filter argument on a column replaces any existing filter on that column. Put West Sales = CALCULATE([Total Sales], Customers[Region] = "West") in a table filtered to North and it still shows West’s ₹71,78,449.

KEEPFILTERS intersects instead: inside a North filter, “North AND West” is empty, so the result is blank. Use it when the measure should respect the user’s slicer.

Common mistakes

• Filtering a measure: CALCULATE([Total Sales], [Total Sales] > 100000) doesn’t work; filter a column, or use FILTER over a table.
• Calculated columns for everything: a column is computed once per row at refresh; a measure responds to filters. Totals and ratios belong in measures.

Practice

Import pbi-intermediate-model.xlsx into Power BI Desktop (free), create the relationships listed at the top of the .dax file, mark the Date table as a date table and add this lesson’s measures. Compare your card and table values with the lesson’s sheet in the expected-results file. Those numbers were worked out independently in Python from the same data, so a mismatch means a filter or relationship to check.