The CALCULATE() function - Theory

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

Instructions

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.