🔧 Hijacking the Context: CALCULATE

If you only learn one DAX function in your entire career, it must be CALCULATE(). It is the single most important function in Power BI.

Analogy: The Time Machine / Alternate Universe

A normal measure is a slave to the visual it is sitting in. If you put Total Sales in a bar chart filtered to the year "2023", the measure can only see 2023 data.

CALCULATE is a time machine. It allows a measure to say: "I don't care what the chart says. I am going to override the rules and look at the universe through my own filters!"

🎯 The Anatomy of CALCULATE

CALCULATE( <Expression>, <Filter1>, <Filter2>, ... )

Expression: The math you want to do (e.g., SUM(Sales[Amount]) or an existing measure [Total Sales]).
Filters: The rules you want to enforce, overriding the visual.

The Smart Way: Let's say you want a Card visual that always shows the sales for France, regardless of what the user clicks on the page. France Sales = CALCULATE( [Total Sales], 'Geography'[Country] = "France" )

Now, even if the user selects "Germany" in a Slicer, that specific measure will ignore them and stubbornly display France's sales!

🌐 The ALL() Function: Removing Filters

While CALCULATE is great for applying specific filters, what if you want to completely erase a filter? You use the ALL() function inside your CALCULATE!

ALL(TableName) returns every single row in a table, ignoring any filters the user has applied on the dashboard.

Why is this useful? Imagine you want to calculate the "Percentage of Grand Total". You need to divide the current row (e.g., "Bikes") by the Grand Total of everything.

Percent of Total = DIVIDE( [Total Sales], CALCULATE([Total Sales], ALL('Sales')) )

This brilliant formula takes the normal filtered sales for Bikes, and divides it by a version of Total Sales that used ALL() to break out of the filter context and grab the grand total!

🔍 The FILTER() Function: Complex Rules

Sometimes, a simple filter like Country = "France" isn't enough. What if you need to filter the data based on a complex math equation? You use the FILTER() function.

FILTER( <Table>, <Condition> )

FILTER scans a table row-by-row and only keeps the rows where the condition is True.
Example: You only want to sum sales for products where the price was above the average price. High Ticket Sales = CALCULATE( [Total Sales], FILTER('Products', 'Products'[Price] > [Overall Avg Price]) )

🔗 Navigating Relationships in DAX

Sometimes you need to pull data from a different table in the middle of a DAX calculation.

RELATED(ColumnName) Pulls a value from a Dimension table (the "1" side) into a Fact table (the "Many" side). Think of this as the exact equivalent of Excel's VLOOKUP.
USERELATIONSHIP(ColumnName1, ColumnName2) Remember those Inactive (dashed) relationship lines from Chapter 6? USERELATIONSHIP is used inside a CALCULATE to temporarily wake up an inactive line for a single calculation! Example: Sales by Ship Date = CALCULATE( [Total Sales], USERELATIONSHIP('Sales'[ShipDate], 'Calendar'[Date]) )

🏋️ Practice Drill

Let's build a static target!

Task:

text

// Try answering these:
1. Create a new measure using `CALCULATE`.
2. Assuming you have a table called `Financials` with a column called `Country`, write this measure:
   `Canada Target = CALCULATE( SUM(Financials[Sales]), Financials[Country] = "Canada" )`
3. Add a Slicer to your page for `Country`.
4. Drop your normal `Total Sales` measure and your new `Canada Target` measure onto the canvas in Card visuals.

💡 Click for Solutions

Click "France" on the slicer. Notice how your normal Total Sales card changes to show France's numbers, but your Canada Target card stubbornly refuses to change and keeps showing Canada's numbers? You have successfully hijacked the Filter Context!

← 🔢 Core DAX Functions | Next Topic → ⏱️ Iterators and Time Intelligence