About this lesson
CALCULATE() is the super-charged SUM(anything) function, and mastering it is the secret to truly mastering Power BI's formula language. In this module we will show how it works, as well as how it impacts basic measure calculation.
There are no related exercise files for this lesson.
The CALCULATE() Function – Theory
Understanding CALCULATE(), the most powerful function in DAX.
When to use
When you need to create or debug a CALCULATE() function to get the correct results.
Key concepts around measure calculation:
- Every measure result is evaluated independently
- Each measure starts with the raw and unfiltered data tables
- The filter context is calculated normally
- Filters declared inside the CALCULATE() function are then applied, adjusting the filter context
- Filters that are not already in the filter context are added
- Filters that are already in the filter context are overridden
- The revised filter context is then applied to the source tables, reducing the number of rows to use in the calculation
- The Measure’s arithmetic signature is applied to whatever rows are left
What contributes to Filter Context?
- The intersection of any fields used in the current visual (each data point is a cross section that inherits the rows/columns/axis/legend, etc..)
- Any selected data points in other visuals
- Selections made in any (all) slicers
- Page or report level filter selections
- Filters declared in the CALCULATE() function
Hints & tips
- AND filters can be used inside CALCULATE() using a double & character:
- Lunch Sales = CALCULATE([Sales],Sales[Hour]>7&&Sales[Hour]<=11)
- Or filters can be used inside CALCULATE() using a double | character:
- Open Tickets = CALCULATE([Ticket Count],Tickets[Status]=”Open”||Ticket[Status]-“Pending”)
- If you are trying to compare more complex operations you may need to nest a FILTER() function in CALCULATE’s filters area
- Watch your data types… if your years are numeric, you need to compare against a number. If the data type is text, you’ll need to wrap the value to compare in quotes
Lesson notes are only available for subscribers.