Mastering DAX in Power BI – The Ultimate Guide for Data Analysts - PART II : Deep Dive into DAX - Context Manipulation

When working with Power BI, one of the most powerful concepts you’ll encounter is Context Manipulation. In business, it’s rare that we always want to see data in its raw, default context. Most of the time, we need to change the way filters, relationships, and calculations behave to answer specific questions. That’s where Context Manipulation functions step in — they let you override, remove, or adjust filters so you can calculate exactly what you need.

Why Context Manipulation is Important in Business

Imagine you are a Sales Manager looking at current month sales for each region. By default, Power BI applies the filter for “Region” when you click on a chart. But sometimes you want to:

• Compare a region’s sales against the total sales for all regions (ignoring the region filter).

• Show sales for the same product across multiple years regardless of the current date filter.

• Keep certain filters but remove others to see a bigger picture.

  
  

In all these cases, context manipulation allows you to change the “rules” of how the data is being filtered.

Common Context Manipulation Functions in DAX

Here’s a table of commonly used Context Manipulation functions and what they do:

Function	Purpose
CALCULATE()	Changes the filter context for a calculation.
CALCULATETABLE()	Changes the filter context for a table expression.
REMOVEFILTERS()	Removes filters from specific columns/tables.
ALL()	Ignores all filters from a column/table.
ALLSELECTED()	Removes filters except those applied via visuals (keeps interactive context).
ALLNOBLANKROW()	Ignores filters and removes any blank rows from relationships.
KEEPFILTERS()	Adds filters without removing existing ones.
USERELATIONSHIP()	Forces DAX to use an inactive relationship for a calculation.
VALUES()	Returns a one-column table of unique values from a column.

Context Manipulation – 4 Main Sub-Categories

Sub-Category	Purpose	Functions
1. Filter Context Transition	Change or create a new filter context for the calculation. Most other filter functions are often used inside CALCULATE.	CALCULATE(), ALLSELECTED()
2. Filter Override	Completely ignore or replace existing filters in the calculation.	ALL(), ALLNOBLANKROW(), REMOVEFILTERS()
3. Filter Preservation / Modification	Keep existing filters or add more without removing the old ones.	KEEPFILTERS(), VALUES()
4. Relationship Control	Force the use of a specific relationship in the model.	USERELATIONSHIP()

How this helps in learning:

When you see a DAX formula with these functions, you can quickly identify what type of context manipulation is happening:

• Is it changing the whole context? → Filter Context Transition

• Is it removing filters? → Filter Override

• Is it adding or keeping filters? → Filter Preservation

• Is it switching relationships? → Relationship Control

  
  

Filter Context Transition in DAX

In Power BI, filter context transition happens when a row context (coming from iterators like SUMX, FILTER, ADDCOLUMNS, etc.) is converted into a filter context so that your measure or expression can work correctly. Think of it like this:

"We’re working row by row, but we need the calculation to behave as if a filter was applied for each row."

Why This Matters in Business

This is crucial when your calculation depends on related tables or needs to respect relationships while looping through rows. For example:

• Calculating each patient’s total bill while iterating through all patients.

• Summing sales per product when each product comes from a related table.

  
  

Without filter context transition, the calculation might ignore filters and give wrong totals.

Filter Context Transition with CALCULATE

CALCULATE is one of the most important DAX functions, and one of the key things it does is trigger a filter context transition when used inside a row context.

The CALCULATE syntax in DAX is:

CALCULATE( 
<scalar_expression>, -- required: must return a single value 
<filter1>, -- optional: Boolean filter, table expression, or filter-modifying function 
<filter2>, ... -- optional: more filters )

• First argument → a scalar value (measure, or an aggregated column).

  
  

• Subsequent arguments →

  
  

  • Boolean filter → Table[Column] = Value (works best in the same table or related tables).

  • Table expression → FILTER(Table, condition).

  • Filter-modifying function → ALL(...), REMOVEFILTERS(...), KEEPFILTERS(...), etc.

    
    

• Multiple filters are combined with logical AND.

  
  

• Works in filter context — changes existing filters before evaluating the scalar expression.

  
  

Example:

AvgSales_West = CALCULATE( 
AVERAGE(Sales[Amount]), -- scalar expression 
Sales[Region] = "West", -- Boolean filter 
FILTER(Products, Products[Price] > 500), -- table filter
ALL(Customers)                           -- filter-modifying function 
)

Scalar Expressions

• Always return exactly one value in the current filter or row context.

• Can return any data type — number, text, date, boolean, or BLANK().

• A bare column is not a scalar expression; it must be aggregated or wrapped in a function that produces one value.

• Measures are valid scalar expressions because they already return a single value.

• For numeric values, use aggregations or iterators (SUM, AVERAGE, MAX, SUMX, etc.) to produce one value.

• For text values, use functions that ensure a single return (SELECTEDVALUE, FIRSTNONBLANK, MIN, etc.).

• BLANK values can be returned; use COALESCE() to replace them with a default.

Boolean Filters

• Work best when all columns are from the same table or from directly related tables with an active relationship.

• If columns are from different, unrelated tables, the filters work independently and don’t affect each other.

• Cannot mix columns from different tables in a single Boolean filter unless wrapped in FILTER() or linked with RELATED().

• Multiple Boolean filters in the same CALCULATE are combined with logical AND.

• Case-insensitive for text comparisons.

• If the condition finds no matches, it returns BLANK for the calculation.

Table Filters

• Return type: Always a table, even if that table has only 1 row or 1 column.

• Input: Takes a table/table expression as its first argument.

• Condition: Second argument is a Boolean expression evaluated row-by-row on that table.

• Usage: Often wrapped in another function (like CALCULATE, SUMX, COUNTROWS) because most DAX functions can’t directly return a table.

• Relationship effect: If the table is connected to other tables in your data model, filtering it with FILTER() will also limit the related tables automatically.

  Example:

  • If you filter Orders to only 2024 sales, the related Customers table will also show only customers who bought something in 2024.

FILTER – all the useful patterns

• Basic row filter: 

Age_greater_than_70 = CALCULATE(
SUM(Patient_Details[age]),
FILTER(
Patient_Details,Patient_Details[age]>70))

• Multiple conditions (AND/OR):

Age_greater_than_70 = CALCULATE(
SUM(Patient_Details[age]),
FILTER(
Patient_Details,
Patient_Details[age]>70 && Patient_Details[handedness] = "LEFT" ))

• Use measures in conditions (dynamic):

avg_prepregnant_weight = AVERAGE(patient_info[prepregnant_weight])

Prepregnancy_weight_analysis = CALCULATE( 
[avg_prepregnant_weight],
FILTER(
Patient_Details,
patient_info[age_years_old]>30))

Inside FILTER() with a measure, use CALCULATE() to make the measure re-evaluate per row.

• Use with CALCULATE to affect results (like above examples)

  So FILTER() chooses which rows, and CALCULATE does the math only on them.

  
  

• Filter an “unfiltered” table (override context):

  FILTER(ALL(Sales), Sales[Amount] > 1000) (works even if the report page is heavily sliced)

  
  

• Ignore/keep specific filters:

  FILTER(REMOVEFILTERS(Sales[Year]), Sales[Amount] > 1000)

  FILTER(ALLEXCEPT(Sales, Sales[Region]), Sales[Amount] > 1000)

  
  

• Work with related tables (RELATED):

  FILTER(Sales, RELATED(Products[Category]) = "Bikes")

  Relationship already exists — so why RELATED()?

  • Yes, if a relationship is established in the model, DAX can use it in many cases without you writing extra code.

  • BUT — inside a FILTER() that is looping row-by-row over the Sales table, you are in row context, not filter context.

  • In row context, DAX only “sees” the columns of the current table (Sales in this case).

  • If you need a column from another table (Products), you must explicitly “look it up” — and RELATED() is the lookup function that uses the relationship to get it.

  
  

• Link unrelated tables (TREATAS):

  CALCULATE([Total Sales], TREATAS({ "West" }, Regions[Region]))

  
  

• Virtual tables as input (combine tables):

  FILTER(CROSSJOIN(VALUES(Customers[Region]),VALUES(Products[Category])),Products[Category]="Bike")

  
  

• Group then filter groups (SUMMARIZE + FILTER):

  FILTER(SUMMARIZE(Sales, Sales[Region], "Tot", SUM(Sales[Amount])), [Tot] > 100000)

  
  

• Add computed columns then filter (ADDCOLUMNS):

  FILTER(ADDCOLUMNS(Products, "WithTax", Products[Price]*1.05), [WithTax] > 100)

  
  

• Top/Bottom N logic (RANKX/TOPN):

  FILTER(Sales, RANKX(ALL(Sales), Sales[Amount]) <= 5)or TOPN(5, Sales, Sales[Amount]) as the table argument to CALCULATE

  
  

• Text filters (contains/starts with):

  FILTER(Customers, CONTAINSSTRING(Customers[Email], "@gmail"))FILTER(Products, LEFT(Products[SKU], 3) = "ABC")

  
  

• Date/window filters:

  FILTER(Sales, Sales[Date] >= DATE(2025,1,1) && Sales[Date] < DATE(2025,2,1))

  
  

• IN / set membership:

  FILTER(Orders, Orders[Status] IN {"Open","Pending"})

  
  

• Blank handling:

  FILTER(Patients, NOT ISBLANK(Patients[BMI]))

  
  

• Count/filter pattern:

  COUNTROWS(FILTER(Patients, Patients[Age] > 60))

  
  

• Exists/relationship-aware filters (RELATEDTABLE, INTERSECT):

  FILTER(Customers, COUNTROWS(RELATEDTABLE(Orders)) > 0)

  
  

• Performance tip (prefer Boolean args when simple):

  Use CALCULATE([Total], Table[Col] = Value) for simple equality; switch to FILTER() for multi-condition, measure-based, or cross-table logic.

  
  

• Remember: FILTER() always returns a table — use it inside CALCULATE, or wrap with COUNTROWS, SUMX, etc., to get a single value.

Filter Override in DAX

When you create a measure in Power BI, it usually respects the filters applied in your visuals, slicers, or pages. Filter Override is when you tell DAX:

“Ignore some (or all) of those filters and calculate as if they weren’t there.”

This is super helpful in business when you need totals, benchmarks, or comparisons that are independent of current selections.

Filter Override Example : ALL – Handedness Slicer & BMI Trends

Scenario:

You have:

• Slicer: Handedness (Left, Right)

• Chart: Shows Patient ID vs. Average BMI trends over time.

  
  

When someone selects Handedness = Left, the chart normally shows only Left-handed patients.

But now, your doctor also wants to see:

“What is the overall average BMI trend for all patients, regardless of handedness?”

This is where Filter Override comes in — you’ll tell DAX to ignore the handedness filter while calculating that “Overall BMI” line.

• Create the measure:

  Overall_Avg_BMI = CALCULATE( Patient_Details[Average BMI], ALL(Patient_Details[handedness]) )

  
  

• Add this measure to the chart. Drop it into the chart along with the normal Average BMI.

  
  

• Now we will see the measure appear in the chart.

• This value will not change when the Handedness slicer selection changes.

For our BMI trend example, using ALL() inside the measure ensures the overall average line stays fixed, no matter which handedness is selected in the slicer. This allows users to compare each group’s BMI trend against a constant overall benchmark.

Using ALLNOBLANKROW()

Overall_Avg_BMI = CALCULATE( [Average BMI], ALLNOBLANKROW( Patient_Details[handedness] ) )

• Purpose: Ignores handedness filters and removes any blank rows from relationships.

• Effect: If a patient’s handedness is missing (blank), those records will not be included in the overall BMI calculation. ALL will include blank rows in the calculation, while ALLNOBLANKROW will exclude them. both ALL() and ALLNOBLANKROW() can be used inside or outside of CALCULATE().

  
  

Using REMOVEFILTERS()

Overall_Avg_BMI = CALCULATE( [Average BMI], REMOVEFILTERS( Patient_Details[handedness] ) )

• Purpose: Explicitly removes filters from handedness before calculation.

• Effect: Same as ALL() for this scenario—average stays fixed regardless of slicer—but it's more semantically clear that you’re “removing” the filter. REMOVEFILTERS works only inside CALCULATE() to clear filters from specific columns or tables.

In short, Filter Override lets you ignore or replace existing filters to see the overall picture. It’s useful when you want totals or benchmarks that remain unchanged, no matter what slicers or filters are applied.