Filter Context

What You'll Learn

• What filter context is
• How context affects calculations
• Row context vs filter context
• Controlling context with DAX

Filter Context Explained

Filter context = the filters currently applied to your data.

Example: Total Sales = SUM(Sales[Amount])

No filters: Shows all sales Region = "East": Shows only East sales Year = 2024: Shows only 2024 sales Both: Shows East sales in 2024

Where Filters Come From

Slicers: User selections

Visual filters: Filter pane settings

Cross-filtering: Clicking other visuals

Page/Report filters: Background filters

All combine together!

Row Context

Different from filter context!

Row context: Evaluating one row at a time

Happens in:

• Calculated columns
• Iterator functions (SUMX, AVERAGEX)

Example: Profit = [Revenue] - [Cost]

Calculates per row, has row context

Context Transition

Issue: Calculated columns don't create filter context

Solution: Wrap in CALCULATE to convert row to filter context

Example: Total Customer Sales = CALCULATE( SUM(Sales[Amount]), Sales[CustomerID] = EARLIER(Sales[CustomerID]) )

CALCULATE Function

Most powerful DAX function!

Purpose: Modify filter context

Syntax: CALCULATE(expression, filter1, filter2)

Add Filters

Filter to specific value: USA Sales = CALCULATE([Total Sales], Customers[Country] = "USA")

Multiple filters: Premium USA = CALCULATE( [Total Sales], Products[Category] = "Premium", Customers[Country] = "USA" )

Remove Filters

Remove all filters: All Sales = CALCULATE([Total Sales], ALL(Sales))

Remove from one column: All Regions = CALCULATE([Total Sales], ALL(Customers[Region]))

Remove except: All Except Date = CALCULATE([Total Sales], ALLEXCEPT(Sales, Calendar[Date]))

Filter Propagation

Filters flow through relationships:

One-to-Many relationship:

• Filter on "one" side filters "many" side
• Filter on "many" doesn't filter "one" (by default)

Cross-filter direction:

• Single: One way only
• Both: Bidirectional (use carefully!)

ALL Family Functions

ALL

Remove all filters: ALL(TableOrColumn)

Example: Grand Total = CALCULATE([Total Sales], ALL(Sales))

ALLSELECTED

Remove only visual filters: Keeps slicers and page filters!

Use for: % of visible total

ALLEXCEPT

Remove all except specified: ALLEXCEPT(Table, Column1, Column2)

FILTER Function

Create custom filter: High Sales = CALCULATE( [Total Sales], FILTER(Sales, Sales[Amount] > 1000) )

Loops through rows: More flexible than simple filters

KEEPFILTERS

Respect existing filters: CALCULATE([Total Sales], KEEPFILTERS(Products[Category] = "Premium"))

Instead of replacing filters, adds to them

VALUES and ALL

VALUES: Returns filtered list

ALL: Returns everything (ignores filters)

Example: Selected Products = COUNTROWS(VALUES(Products[ProductID]))

Try This Exercise

Create these:

1. Total for selected items: Selected Total = [Total Sales]

2. Total for everything: Grand Total = CALCULATE([Total Sales], ALL(Sales))

3. Percentage of total: % of Total = DIVIDE([Total Sales], [Grand Total])

4. Add to table with Region

5. Test with Region slicer

Common Patterns

Percentage of Parent

% of Category = DIVIDE( [Total Sales], CALCULATE([Total Sales], ALL(Products[ProductName])) )

Filtering by Multiple Values

Top 3 Products = CALCULATE( [Total Sales], TOPN(3, ALL(Products), [Total Sales]) )

Next Steps

Learn the CALCULATE function in depth!

Tip: Understanding context = understanding DAX. This is the foundation!