🚀 Data Analyst Roadmap — Part 26

POWER BI LEVEL 5 — DAX: CALCULATE(), FILTERS & CONTEXT

If you understand CALCULATE(), DAX becomes much easier.

The most important idea:

👉 CALCULATE() changes the filter context in which a measure is evaluated.

Example:

Total Sales =

SUM(Sales)[SalesAmount]

Now suppose you want sales only for the West region:

West Sales = CALCULATE( [Total Sales], Sales[Region] = "West" )

CALCULATE() takes the existing calculation and applies an additional filter.

🔹 1. CALCULATE() with multiple filters

You can apply multiple conditions:

West Electronics Sales = CALCULATE( [Total Sales], Sales[Region] = "West", Sales[Category] = "Electronics" )

This calculates sales where:

Region = West

AND

Category = Electronics

🔹 2. REMOVEFILTERS()

Sometimes you don't want a slicer or visual filter to affect your calculation.

Example:

Total Sales All Regions = CALCULATE( [Total Sales], REMOVEFILTERS(Sales[Region]) )

If a report is filtered to:

Region → West

this measure still shows sales across all regions.

🔹 3. ALL()

ALL() can also remove filters.

Example:

Total Sales All Regions = CALCULATE( [Total Sales], ALL(Sales[Region]) )

A common use is calculating percentage of total.

Sales % of Total = DIVIDE( [Total Sales], CALCULATE( [Total Sales], ALL(Sales[Region]) ) )

If West has ₹20 lakh sales and all regions have ₹100 lakh:

Sales % of Total = 20%

🔹 4. ALLSELECTED()

ALLSELECTED() is useful when you want to respect the user's overall selections but ignore a visual-level grouping.

Example:

Sales % of Selected Regions = DIVIDE( [Total Sales], CALCULATE( [Total Sales], ALLSELECTED(Sales[Region]) ) )

If the user selects:

West + South

the calculation can compare each region against the total of the selected regions rather than the entire dataset.

🔹 5. KEEPFILTERS()

By default, CALCULATE() can replace an existing filter on the same column.

KEEPFILTERS() tells DAX to preserve the existing filter and apply the new condition on top of it.

Example:

CALCULATE( [Total Sales], KEEPFILTERS(Sales[Category] = "Electronics") )

Think of it as:

Existing filters

+

New filter

instead of replacing the existing filter.

🔹 6. FILTER()

FILTER() creates a filtered table based on a condition.

Example:

High Value Sales = CALCULATE( [Total Sales], FILTER( Sales, Sales[SalesAmount] > 10000))

This calculates sales from transactions greater than ₹10,000.

Use FILTER() when the filtering logic is more complex than a simple column = value condition.

🔹 7. Context Transition

This sounds complicated, but the basic idea is simple.

DAX has two important contexts:

👉 Row Context

Works with the current row.

👉 Filter Context

Determines which data is included in a calculation.

CALCULATE() has a special behavior:

It can convert row context into filter context.

This is called:

Context Transition

You will encounter this especially when using CALCULATE() inside calculated columns or iterator functions such as SUMX(), FILTER(), etc.

💡 A simple way to remember CALCULATE():

CALCULATE() = "Calculate this measure, but under these filter conditions."

For example:

[Total Sales]

↓

CALCULATE(

[Total Sales],

Region = "West"

)

↓

"Calculate Total Sales, but only for West."