TECH **Power BI Evaluation Context: Row vs Filter Context – Zero-Fluff Study Guide**

Power BI Evaluation Context: Row vs Filter Context – Zero-Fluff Study Guide

1. What This Is & Why It Matters

Evaluation context in Power BI determines how DAX formulas calculate results. It’s the invisible engine that decides whether a measure sums all rows, filters by a slicer, or respects a row-level condition.

Why it matters in production:
- Broken reports: If you don’t understand evaluation context, your measures will return wrong numbers—silently. A sales dashboard might show "Total Revenue = $1M" when it should be "$500K" because a filter wasn’t applied.
- Performance nightmares: Misusing context can force Power BI to scan entire tables instead of using optimized filters, turning a 2-second refresh into a 2-minute crawl.
- Debugging hell: When a measure "suddenly" stops working after adding a slicer, 90% of the time it’s an evaluation context issue.

Real-world scenario:
You inherit a Power BI report from a colleague. The "Average Order Value" measure works fine in a table visual but returns the same number in a card visual—no matter what filters you apply. The problem? The measure ignores filter context because it was written with CALCULATE incorrectly.

2. Core Concepts & Components

1. Row Context

• Definition: The context created by iterating over rows in a table (e.g., in calculated columns or FILTER functions).
• Production insight: Row context is not automatically applied to measures. If you write SUM(Sales[Amount]) in a calculated column, it sums the entire Sales table for each row—not what you want.
• Example:
  dax // Calculated column (row context exists) Sales[Tax] = Sales[Amount] * 0.08 Here, Sales[Amount] refers to the current row’s value.

2. Filter Context

• Definition: The set of filters applied to a calculation (e.g., from slicers, visuals, or CALCULATE).
• Production insight: Filter context overrides row context. If a slicer filters Sales[Year] = 2023, all measures respect this unless explicitly ignored.
• Example:
  dax // Measure (filter context exists) Total Sales = SUM(Sales[Amount]) If a slicer filters Year = 2023, this measure sums only 2023 sales.

3. CALCULATE Function

• Definition: The only DAX function that modifies filter context. It can add, remove, or override filters.
• Production insight: CALCULATE is the most powerful (and dangerous) function in DAX. Misuse it, and your measures will return inconsistent results.
• Example:
  dax Sales 2023 = CALCULATE(SUM(Sales[Amount]), Sales[Year] = 2023) This forces the measure to ignore all other filters and only sum 2023 sales.

4. Context Transition

• Definition: When row context is converted to filter context (e.g., inside CALCULATE or FILTER).
• Production insight: This is why CALCULATE(SUM(Sales[Amount]), Sales[Region] = "West") works—Sales[Region] = "West" becomes a filter.
• Example:
  dax // Context transition in action West Sales = CALCULATE(SUM(Sales[Amount]), Sales[Region] = "West")

5. ALL Function

• Definition: Removes all filters from a table or column.
• Production insight: ALL is often misused to "fix" measures that ignore filters. Overuse it, and your measures will return the same number everywhere.
• Example:
  dax // Total sales, ignoring all filters Total Sales All Years = CALCULATE(SUM(Sales[Amount]), ALL(Sales[Year]))

6. FILTER Function

• Definition: Returns a subset of rows (creates row context) and can be used inside CALCULATE to modify filter context.
• Production insight: FILTER is slow. Use it only when necessary—prefer CALCULATE with direct filters.
• Example:
  ```dax // Slow: Using FILTER inside CALCULATE High Value Sales = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Amount] > 1000))

// Faster: Direct filter in CALCULATE High Value Sales = CALCULATE(SUM(Sales[Amount]), Sales[Amount] > 1000) ```

7. EARLIER Function

• Definition: Refers to the "outer" row context in nested row contexts (e.g., in calculated columns with nested FILTER).
• Production insight: Rarely needed in measures. If you’re using EARLIER, you’re probably overcomplicating things.
• Example:
  dax // Calculated column: Compare current row to previous row Sales[Prev Amount] = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[ID] = EARLIER(Sales[ID]) - 1))

8. HASONEVALUE Function

• Definition: Checks if a column has only one distinct value in the current filter context.
• Production insight: Useful for conditional measures (e.g., "Show this only if one product is selected").
• Example:
  dax // Show product name only if one product is selected Selected Product = IF(HASONEVALUE(Products[Name]), VALUES(Products[Name]), "Multiple Products")

3. Step-by-Step Hands-On: Debugging a Broken Measure

Prerequisites

• Power BI Desktop installed.
• A dataset with at least two tables: Sales (with columns Amount, Year, Region) and Products (with ProductID, Name).

Task: Fix a Measure That Ignores Slicers

You’re given a measure that always shows total sales, even when a slicer filters by Year or Region.

Step 1: Reproduce the Problem

1. Create a table visual with Year and Total Sales (a measure defined as SUM(Sales[Amount])).
2. Add a slicer for Year.
3. Observe: The measure ignores the slicer and always shows the grand total.

Step 2: Identify the Issue

• The measure SUM(Sales[Amount]) respects filter context by default.
• If it’s ignoring slicers, something is overriding the filter context (e.g., ALL or CALCULATE with no filters).

Step 3: Check the Measure Definition

1. Go to the "Model" view.
2. Find the Total Sales measure.
3. If it looks like this, you’ve found the problem:
   dax
Total Sales = CALCULATE(SUM(Sales[Amount]), ALL(Sales))
   The ALL(Sales) removes all filters from the Sales table.

Step 4: Fix the Measure

Replace the measure with:

Total Sales = SUM(Sales[Amount])

Now the measure will respect slicers.

Step 5: Verify the Fix

1. Add a slicer for Year and select 2023.
2. The table visual should now show only 2023 sales.

Step 6: Add a "Grand Total" Measure (Optional)

If you also want a measure that ignores slicers (e.g., for comparisons), create a new measure:

Total Sales All Years = CALCULATE(SUM(Sales[Amount]), ALL(Sales[Year]))

Now you can use both measures in the same visual.

4. ? Production-Ready Best Practices

Security

• Least privilege for measures: If a measure should never ignore filters (e.g., for compliance), avoid ALL and REMOVEFILTERS.
• Row-level security (RLS): Test measures with RLS enabled. Some measures may break if they rely on ALL to bypass security filters.

Performance

• Avoid FILTER in measures: Use CALCULATE with direct filters instead (e.g., CALCULATE(SUM(...), Table[Column] = "Value")).
• Use KEEPFILTERS: If you need to add a filter without overriding existing ones, use CALCULATE(..., KEEPFILTERS(...)).
• Prefer measures over calculated columns: Calculated columns create row context and bloat your model. Use measures for aggregations.

Reliability & Maintainability

• Naming conventions: Prefix measures that ignore filters with ALL_ (e.g., ALL_Total Sales).
• Document context assumptions: Add comments to measures explaining what filters they respect/ignore.
  dax // Respects all filters except Year Total Sales = CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS(Sales[Year]))

Observability

• Use SELECTEDVALUE for debugging: If a measure isn’t filtering as expected, check the current filter context: dax Debug Year = SELECTEDVALUE(Sales[Year], "No Year Selected")
• Test with ISFILTERED: Verify if a column is being filtered: dax Is Year Filtered = ISFILTERED(Sales[Year])

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus (PL-300)

Typical Question Patterns

1. Context identification:
2. "What is the evaluation context for this measure in a table visual?"

3. Answer: Filter context (from visuals, slicers, and relationships).

4. CALCULATE behavior:

5. "What does CALCULATE(SUM(Sales[Amount]), ALL(Sales[Year])) do?"

6. Answer: Sums Amount while ignoring any filters on Year.

7. Row vs filter context:

8. "In a calculated column, what does SUM(Sales[Amount]) return?"

9. Answer: The sum of all rows in Sales[Amount] (row context doesn’t apply to measures).

10. EARLIER usage:

11. "When would you use EARLIER?"
12. Answer: In calculated columns with nested row contexts (rare in measures).

Key ⚠️ Trap Distinctions

• ALL vs REMOVEFILTERS:
• ALL(Sales) removes all filters from the Sales table.
• REMOVEFILTERS(Sales[Year]) removes only filters on Year.
• FILTER vs direct filters in CALCULATE:
• FILTER creates row context and is slower.
• Direct filters (e.g., Sales[Year] = 2023) are faster and preferred.

Common Scenario-Based Question

"You have a measure Total Sales = SUM(Sales[Amount]). When you add a slicer for Year, the measure still shows the grand total. What’s the most likely cause?" - Answer: The measure is wrapped in CALCULATE(..., ALL(Sales)) or ALL(Sales[Year]).

7. ? Hands-On Challenge (with Solution)

Challenge

Create a measure that shows: - The total sales for the current year (respecting slicers).
- The total sales for all years (ignoring slicers).
- The difference between the two.

Solution

// Current year sales (respects slicers)
Current Year Sales = SUM(Sales[Amount])

// All years sales (ignores slicers)
All Years Sales = CALCULATE(SUM(Sales[Amount]), ALL(Sales[Year]))

// Difference
Sales Difference = [Current Year Sales] - [All Years Sales]

Why it works:
- Current Year Sales respects filter context (e.g., slicers).
- All Years Sales uses ALL(Sales[Year]) to ignore year filters.
- The difference measure dynamically updates based on slicer selections.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. Microsoft DAX Guide – Evaluation Context
2. SQLBI – Understanding Evaluation Context
3. Power BI Guided Learning – DAX
4. Book: The Definitive Guide to DAX (Marco Russo & Alberto Ferrari) – Chapter 4 (Evaluation Context).

Final Tip:
When in doubt, test your measures in a table visual with different slicers. If the numbers don’t change as expected, you’ve got an evaluation context issue. Use SELECTEDVALUE and ISFILTERED to debug.