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
Understand Evaluation Context
The MOST Important DAX Concept
Use of ALL() or REMOVEFILTERS() to compute Ratios
My Notes
Action Items
[ ]
Resources & Links
Slides
� Ő≈� ‹ Ő� ϛ 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!