This session unfolds evaluation context and the logic behind CALCULATE(). Through examples, we'll demystify how row context, filter context, and CALCULATE() work together to produce dynamic calculation results. You'll gain confidence to tackle advanced calculations and optimize measure performance.
Key Takeaways
Influence our SQL roadmap and ensure
Unfold the mechanics of CALCULATE
Understand where to use CALCULATE
Understand what CALCULATE is doing behind the scenes
� Ő≈� ‹ Ő� ϛ MARCH 16 - 20, 2026 DAX 201: CALCULATE() Unfolding the Swiss Army Knife of DAX Functions Michael Hewitt Senior Business Intelligence Manager Hunt Brothers Pizza / Hunt Advantage Group Nashville, Tennessee, USA Sound off. The mic is all yours. Influence the product roadmap. Join the Fabric User Panel Join the SQL User Panel Share your feedback directly with our Fabric product group and researchers. Influence our SQL roadmap and ensure it meets your real-life needs https://aka.ms/JoinFabricUserPanel https://aka.ms/JoinSQLUserPanel Session Objectives • Unfold the mechanics of CALCULATE • Understand where to use CALCULATE • Understand what CALCULATE is doing behind the scenes • Understand Evaluation Context DAX Learning Path DAX 101 You Are Here DAX 200 DAX Basics Calculated Columns Measures Aggregations Variables Evaluation Context Row Context Filter Context Context Transition Forcing Filter Context Common Table Functions Use Cases Adding Filters Removing Filters Mixing Table Functions CALCULATE = Filter Context Filter arguments are tables Context Transition Hands-on Examples DAX 300 Time Intelligence Marking Date Table Turn off Auto-Calendar Standard Functions Calendar Based Time Intelligence DAX 400 Complex DAX Patterns Custom Time Intelligence DAX Queries Expanded Tables Propagating Relationships Vs. Mirroring filters through expanded table concept. Many to Many Classic M2M Relationships Propagate Row Context? Propagate Filter Context DAX in action! How do we go from this… … to this? Unfolding the Mechanics of CALCULATE() Order of Evaluation Priority: First to Last KEEPFILTERS ADD EXPLICIT FILTERS ORIGINAL REPORT FILTERS Report Level Filters and User Selections TABLE ROWS > FILTERS: Context Transition Remove and Replace matching filters from Level 1 Remove and Replace filters from Layers 1-3 CALCULATE MODIFIERS Modify Original Filters From Layers 1-2 REMOVEFILTERS() USERELATIONSHIP() CROSSFILTER() Order of Evaluation Priority: First to Last KEEPFILTERS ADD EXPLICIT FILTERS ORIGINAL REPORT FILTERS Report Level Filters and User Selections TABLE ROWS > FILTERS: Context Transition Remove and Replace matching filters from Level 1 Remove and Replace filters from Layers 1-3 CALCULATE MODIFIERS Modify Original Filters From Layers 1-2 REMOVEFILTERS() USERELATIONSHIP() CROSSFILTER() Order of Evaluation Priority: First to Last KEEPFILTERS ADD EXPLICIT FILTERS ORIGINAL REPORT FILTERS Report Level Filters and User Selections TABLE ROWS > FILTERS: Context Transition Remove and Replace matching filters from Level 1 Remove and Replace filters from Layers 1-3 CALCULATE MODIFIERS Modify Original Filters From Layers 1-2 REMOVEFILTERS() USERELATIONSHIP() CROSSFILTER() Attract original report filters (outer & inner) Order of Evaluation Priority: First to Last KEEPFILTERS ADD EXPLICIT FILTERS ORIGINAL REPORT FILTERS Report Level Filters and User Selections Attract original report filters (outer & inner) TABLE ROWS > FILTERS: Context Transition Remove and Replace matching filters from Level 1 Remove and Replace filters from Layers 1-3 CALCULATE MODIFIERS Modify Original Filters From Layers 1-2 REMOVEFILTERS() USERELATIONSHIP() CROSSFILTER() Attract original table filters Context Transition Order of Evaluation Priority: First to Last KEEPFILTERS ADD EXPLICIT FILTERS ORIGINAL REPORT FILTERS Report Level Filters and User Selections Attract original report filters (outer & inner) TABLE ROWS > FILTERS: Context Transition Remove and Replace matching filters from Level 1 Attract original table filters Context Transition Remove and Replace filters from Layers 1-3 CALCULATE MODIFIERS Modify Original Filters From Layers 1-2 REMOVEFILTERS() USERELATIONSHIP() CROSSFILTER() Alter filters CALCULATE Modifiers Order of Evaluation Priority: First to Last KEEPFILTERS ADD EXPLICIT FILTERS ORIGINAL REPORT FILTERS Report Level Filters and User Selections Attract original report filters (outer & inner) TABLE ROWS > FILTERS: Context Transition Remove and Replace matching filters from Level 1 Attract original table filters Context Transition Remove and Replace filters from Layers 1-3 CALCULATE MODIFIERS Modify Original Filters From Layers 1-2 REMOVEFILTERS() USERELATIONSHIP() CROSSFILTER() Alter filters CALCULATE Modifiers Add new table filters Order of Evaluation Priority: First to Last KEEPFILTERS ADD EXPLICIT FILTERS ORIGINAL REPORT FILTERS Report Level Filters and User Selections Attract original report filters (outer & inner) TABLE ROWS > FILTERS: Context Transition Remove and Replace matching filters from Level 1 Attract original table filters Context Transition Remove and Replace filters from Layers 1-3 CALCULATE MODIFIERS Modify Original Filters From Layers 1-2 REMOVEFILTERS() USERELATIONSHIP() CROSSFILTER() Alter filters CALCULATE Modifiers Add new table filters Filter Modifier – KEEPFILTERS() Order of Evaluation Priority: First to Last KEEPFILTERS ADD EXPLICIT FILTERS ORIGINAL REPORT FILTERS Report Level Filters and User Selections Attract original report filters (outer & inner) TABLE ROWS > FILTERS: Context Transition Remove and Replace matching filters from Level 1 Attract original table filters Context Transition Remove and Replace filters from Layers 1-3 CALCULATE MODIFIERS Modify Original Filters From Layers 1-2 REMOVEFILTERS() USERELATIONSHIP() CROSSFILTER() Alter filters CALCULATE Modifiers Add new table filters Filter Modifier – KEEPFILTERS() Challenge Questions (WAIT!? Does that mean there’s a quiz?) Challenge Questions (WAIT!? Does that mean there’s a quiz?) Yes. Challenge Question #1 USERELATIONSHIP() vs. TREATAS() What is unseen here that is causing unexpected results with TREATAS()? USEREL (L3) = CALCULATE ( [Total Sales], USERELATIONSHIP ( fact_Sales[Order Date], dim_Time[Date] ) ) TREATAS (L4) = CALCULATE ( [Total Sales], TREATAS ( VALUES ( dim_Time[Date] ), fact_Sales[Order Date] ) ) TREATAS() Table expression – technically NOT a CALCULATE() modifier Treats the columns of the input table as columns from other tables – DAX.guide Transfers column values as filters to other columns through virtual relationships TREATAS() creates virtual relationships that transfer lineage Challenge Question #2 Slicer not filtering large orders? Order > 250 Bad = CALCULATE ( [Total Sales], fact_Sales[Total Sales] > 250 ) Filter on Total Sales range Challenge Question #2 Slicer not filtering large orders? Order > 250 Bad = CALCULATE ( [Total Sales], fact_Sales[Total Sales] > 250 ) Measure still returns sales amounts over $250 Filter lower sales amounts CALCULATE() in Slow Motion CALCULATE() in Slow Motion We write a measure to calculate total sales and visualize that measure SUM ( fact_Sales[Total Sales] ) CALCULATE() in Slow Motion Under the hood, the corresponding iterator function is used SUM ( fact_Sales[Total Sales] ) SUMX (fact_Sales, fact_Sales[Total Sales] ) Row Context is in play CALCULATE() in Slow Motion Under the hood, an implicit CALCULATE() is added which captures the initial filter context CALCULATE ( SUMX (fact_Sales, fact_Sales[Total Sales] ),
) Filter Context is in play CALCULATE() in Slow Motion Under the hood, an implicit CALCULATE() is added which captures the initial filter context CALCULATE ( SUMX (fact_Sales, fact_Sales[Total Sales] ), CustTable[Name] = “Chris”, dim_Part[Vendor] = “KARPARTS”, dim_Time[Cal Year] = 2024 ) Filter Context is in play Chris CALCULATE() filters the dimension tables CALCULATE ( SUMX (fact_Sales, fact_Sales[Total Sales] ), CustTable[Name] = “Chris”, dim_Part[Vendor] = “KARPARTS”, dim_Time[Cal Year] = 2024 ) Chris KARPARTS CALCULATE() filters the dimension tables CALCULATE ( SUMX (fact_Sales, fact_Sales[Total Sales] ), CustTable[Name] = “Chris”, dim_Part[Vendor] = “KARPARTS”, dim_Time[Cal Year] = 2024 ) Chris KARPARTS CALCULATE() filters the dimension tables CALCULATE ( SUMX (fact_Sales, fact_Sales[Total Sales] ), CustTable[Name] = “Chris”, dim_Part[Vendor] = “KARPARTS”, dim_Time[Cal Year] = 2024 ) Chris KARPARTS Filters flow from dimension tables to the fact table and are applied to the fact table CALCULATE ( SUMX (fact_Sales, fact_Sales[Total Sales] ), CustTable[Name] = “Chris”, dim_Part[Vendor] = “KARPARTS”, dim_Time[Cal Year] = 2024 ) Chris KARPARTS Filtered rows in the fact table are aggregated CALCULATE ( SUMX (fact_Sales, fact_Sales[Total Sales] ), CustTable[Name] = “Chris”, dim_Part[Vendor] = “KARPARTS”, dim_Time[Cal Year] = 2024 ) CALCULATE() in Slow Motion CALCULATE() returns the new filter context back to the visual under the new filter context ORIGINAL REPORT FILTERS Report Level Filters and User Selections TABLE ROWS > FILTERS: Context Transition Remove and Replace matching filters from Level 1 CALCULATE() in Slow Motion CALCULATE() = Filter Context Reusable and dynamic results in our visuals CALCULATE ( SUMX (fact_Sales, fact_Sales[Total Sales] ), CustTable[Name] = “Chris”, dim_Part[Vendor] = “KARPARTS”, dim_Time[Cal Year] = 2024 ) CALCULATE ( SUMX (fact_Sales, fact_Sales[Total Sales] ), CustTable[Name] = “Chris”, dim_Part[Vendor] = “KARPARTS”, dim_Time[Cal Year] = 2024 ) The MOST Important DAX Concept Row Context and Filter Context Work Together! Row Context and Filter Context Work Together CALCULATE ( SUMX (fact_Sales, fact_Sales[Total Sales] ), CustTable[Name] = “Chris”, dim_Part[Vendor] = “KARPARTS”, dim_Time[Cal Year] = 2024 ) Row Context Filter Context Calculate Modifiers Use of ALL() or REMOVEFILTERS() to compute Ratios ALL() or REMOVEFILTERS() as a CALCULATE() argument removes filters placed on columns ALL() is a table function that returns all rows in a tables or all values in a column ignoring any filters REMOVEFILTERS() is not a table function – it is a modifier that clears filters from a table or column CALCULATE ( [Total Sales], ALL ( dim_Part[Vendor] ) ) CALCULATE ( [Total Sales], REMOVEFILTERS ( dim_Part[Vendor] ) ) CALCULATE ( [Total Sales], REMOVEFILTERS ( dim_Part[Vendor], dim_Part[Class] ) ) Calculate Modifiers Use of ALL() or REMOVEFILTERS() to compute Ratios CALCULATE MODIFIERS Modify Original Filters From Layers 1-2 REMOVEFILTERS() USERELATIONSHIP() CROSSFILTER() Calculate Modifiers CROSSFILTER() CALCULATE MODIFIERS Modify Original Filters From Layers 1-2 REMOVEFILTERS() USERELATIONSHIP() CROSSFILTER() CALCULATE ( COUNTROWS ( CustTable ), CROSSFILTER ( fact_Sales[Cust#], CustTable[Cust#], BOTH ) ) Calculate Modifiers CROSSFILTER() CALCULATE MODIFIERS Modify Original Filters From Layers 1-2 REMOVEFILTERS() USERELATIONSHIP() CROSSFILTER() CALCULATE ( COUNTROWS ( CustTable ), CROSSFILTER ( fact_Sales[Cust#], CustTable[Cust#], BOTH ) ) Column Filter Predicates Which DAX expressions are identical when evaluated? ADD EXPLICIT FILTERS HINT: Simple filters ignore other filters on the visual (Color = “Red”) Remove and Replace filters from Layers 1-3 CALCULATE ( [Total Sales], dim_Part[Color] = "Red" ) CALCULATE ( [Total Sales], FILTER ( ALL ( dim_Part[Color] ), dim_Part[Color] = "Red" ) ) CALCULATE ( [Total Sales], FILTER ( VALUES ( dim_Part[Color] ), dim_Part[Color] = "Red" ) ) Column Filter Predicates Which DAX expressions are identical when evaluated? ADD EXPLICIT FILTERS Remove and Replace filters from Layers 1-3 Column Filter Predicates Which DAX expressions are identical when evaluated? ADD EXPLICIT FILTERS HINT: Simple filters ignore other filters on the visual (Color = “Red”) Remove and Replace filters from Layers 1-3 CALCULATE ( [Total Sales], dim_Part[Color] = "Red" ) CALCULATE ( [Total Sales], FILTER ( ALL ( dim_Part[Color] ), dim_Part[Color] = "Red" ) ) CALCULATE ( [Total Sales], FILTER ( VALUES ( dim_Part[Color] ), dim_Part[Color] = "Red" ) ) Table Iterator Table Function Filter expression VALUES() respects filter context Resolving Filter Conflicts Competing filters: Impossible ‘AND’ ADD EXPLICIT FILTERS Remove and Replace filters from Layers 1-3 CALCULATE ( [Total Sales], dim_Part[Color] = "Red", dim_Part[Color] = "Blue“ ) Competing Filters and Nested CALCULATE()s Innermost filter wins KEEPFILTERS Multiple filters in CALCULATE() “merge” In nested CALCULATE()s, inner filter OVERWRITES the outer filter (first evaluated CALCULATE filter) Use KEEPFILTERS() to keep the inner filter from replacing the outer filter – “Intersect” Competing Filters and Nested CALCULATE()s Innermost filter wins Compete Filters 1 = CALCULATE ( [Total Sales], dim_Part[Color] = "Red" "Red", dim_Part[Color] = "Blue" ) Compete Filters2 = CALCULATE ( CALCULATE ( [Total Sales], dim_Part[Color] IN { "Red", "Blue" } ), dim_Part[Color] IN { "Green", "Blue" } ) KEEPFILTERS Compete Filters3 = CALCULATE ( CALCULATE ( [Total Sales], KEEPFILTERS ( dim_Part[Color] IN { "Red", "Blue" } ) ), dim_Part[Color] IN { "Green", "Blue" } ) Competing Filters and Nested CALCULATE()s KEEPFILTERS Innermost filter wins Compete Filters 1 = CALCULATE ( [Total Sales], dim_Part[Color] = "Red", "Red" dim_Part[Color] = "Blue" "Blue“ ) Competing Filters and Nested CALCULATE()s KEEPFILTERS Innermost filter wins Compete Filters2 = CALCULATE ( CALCULATE ( [Total Sales], dim_Part[Color] IN { "Red", "Blue" } ), dim_Part[Color] IN { "Green", "Blue" } ) Competing Filters and Nested CALCULATE()s KEEPFILTERS Innermost filter wins Compete Filters3 = CALCULATE ( CALCULATE ( [Total Sales], KEEPFILTERS ( dim_Part[Color] IN { "Red", "Blue" } ) ), dim_Part[Color] IN { "Green", "Blue" } ) (Not So) Pop Quiz! Challenge Question #1 USERELATIONSHIP() vs. TREATAS() What is unseen here that is causing unexpected results with TREATAS()? USEREL (L3) = CALCULATE ( [Total Sales], USERELATIONSHIP ( fact_Sales[Order Date], dim_Time[Date] ) ) TREATAS (L4) = CALCULATE ( [Total Sales], TREATAS ( VALUES ( dim_Time[Date] ), fact_Sales[Order Date] ) ) Challenge Question #1 USERELATIONSHIP() vs. TREATAS() What is unseen here that is causing unexpected results with TREATAS()? USEREL (L3) = CALCULATE ( [Total Sales], USERELATIONSHIP ( fact_Sales[Order Date], dim_Time[Date] ) ) TREATAS (L4) = CALCULATE ( [Total Sales], TREATAS ( VALUES ( dim_Time[Date]), fact_Sales[Order Date] ) ) Challenge Question #1 USERELATIONSHIP() vs. TREATAS() What is unseen here that is causing unexpected results with TREATAS()? USEREL (L3) = CALCULATE ( [Total Sales], USERELATIONSHIP ( fact_Sales[Order Date], dim_Time[Date] ) ) Active Relationship: fact_Sales[Ship Date] dim_Date[Date] TREATAS (L4) = CALCULATE ( Inactive Relationship: fact_Sales[Order Date]) dim_Date[Date] [Total Sales], TREATAS ( VALUES ( dim_Time[Date]), fact_Sales[Order Date] ) Challenge Question #1 USERELATIONSHIP() vs. TREATAS() What is unseen here that is causing unexpected results with TREATAS()? USEREL (L3) = CALCULATE ( [Total Sales], USERELATIONSHIP ( fact_Sales[Order Date], dim_Time[Date] ) ) Filters follow lineage: Eliminate original report filter to ensure only one filter from Date[Date] dim TREATAS (L4) = CALCULATE ( [Total Sales], TREATAS ( VALUES ( dim_Time[Date]), fact_Sales[Order Date] ) ) Challenge Question #1 USERELATIONSHIP() vs. TREATAS() What is unseen here that is causing unexpected results with TREATAS()? TREATAS (L4) RF = CALCULATE ( [Total Sales], ??? REMOVEFILTERS ( dim_Time[Date] ), TREATAS ( VALUES ( dim_Time[Date] ), fact_Sales[Order Date] ) ) TREATAS (L4) RF = CALCULATE ( [Total Sales], REMOVEFILTERS ( dim_Time[Date] ), TREATAS ( VALUES ( dim_Time[Date] ), fact_Sales[Order Date] ) ) KEEPFILTERS ADD EXPLICIT FILTERS ORIGINAL REPORT FILTERS Report Level Filters and User Selections Attract original report filters (outer & inner) TABLE ROWS > FILTERS: Context Transition Remove and Replace matching filters from Level 1 Attract original table filters Context Transition Remove and Replace filters from Layers 1-3 CALCULATE MODIFIERS Modify Original Filters From Layers 1-2 REMOVEFILTERS() USERELATIONSHIP() CROSSFILTER() Alter filters CALCULATE Modifiers Add new table filters Filter Modifier – KEEPFILTERS() Challenge Question #2 Slicer not filtering large orders? Order > 250 Bad = CALCULATE ( [Total Sales], fact_Sales[Total Sales] > 250 ) Measure still returns sales amounts over $250 Filter lower sales amounts Challenge Question #2 Slicer not filtering large orders? Order > 250 Good = CALCULATE ( [Total Sales], KEEPFILTERS ( ??? fact_Sales[Total Sales]
250 Filters sales amounts up to $150 ) ) Filter lower sales amounts Order of Evaluation Priority: First to Last KEEPFILTERS() builds a barrier around Level 24, preventing each from impacting lower-level filter arguments KEEPFILTERS ADD EXPLICIT FILTERS ORIGINAL REPORT FILTERS Report Level Filters and User Selections Attract original report filters (outer & inner) TABLE ROWS > FILTERS: Context Transition Remove and Replace matching filters from Level 1 Attract original table filters Context Transition Remove and Replace filters from Layers 1-3 CALCULATE MODIFIERS Modify Original Filters From Layers 1-2 REMOVEFILTERS() USERELATIONSHIP() CROSSFILTER() Alter filters CALCULATE Modifiers Add new table filters Filter Modifier – KEEPFILTERS() Order of Evaluation Priority: First to Last KEEPFILTERS ADD EXPLICIT FILTERS ORIGINAL REPORT FILTERS Report Level Filters and User Selections Attract original report filters (outer & inner) TABLE ROWS > FILTERS: Context Transition Remove and Replace matching filters from Level 1 Attract original table filters Context Transition Remove and Replace filters from Layers 1-3 CALCULATE MODIFIERS Modify Original Filters From Layers 1-2 REMOVEFILTERS() USERELATIONSHIP() CROSSFILTER() Alter filters CALCULATE Modifiers Add new table filters Filter Modifier – KEEPFILTERS() ò � جϛ جК�ϛ �� ְ ϛ�ְ ���גּ۶ ϭ ϛ Complete Session Surveys in for your chance to WIN PRIZES!