CALCULATE — the most important DAX function — Power BI Module 5

If you write Power BI seriously, you write CALCULATE every day. It is the function that lets you say 'compute this measure, but with a different filter context'. Every time you've thought 'sum the sales, but only for Enterprise customers' or 'compute this YTD' or 'show this as a percentage of total regardless of slicers' — you're describing a CALCULATE.

The CALCULATE signature

text

CALCULATE(
  <expression>,
  <filter1>,
  <filter2>,
  ...
)

// Each filter modifies the filter context before <expression> evaluates.
// Filters can be Boolean (e.g. Customer[Tier] = "Enterprise") or table
// expressions (e.g. FILTER(Customer, ...) returns a filtered table).

CALCULATE evaluates the expression in a modified filter context. The filters are applied in order; they layer on top of existing filters unless explicitly told otherwise.

ALL — remove all filters on a table or column

text

// Sales regardless of any slicer or filter
Total Sales (unfiltered) = CALCULATE([Total Sales], ALL(Sales))

// Sales regardless of region filter — but other filters apply
Sales ignore region = CALCULATE([Total Sales], ALL(Customer[Region]))

ALL is how you build 'percent of total' calculations. CALCULATE([Total Sales]) / CALCULATE([Total Sales], ALL(Sales)) gives the current row's % of the grand total.

FILTER — replace the filter context with a table expression

text

// Sales above $1000
Large sales = CALCULATE(
  [Total Sales],
  FILTER(Sales, Sales[Amount] > 1000)
)

// More complex: sales from customers with >5 lifetime orders
Loyal customer sales = CALCULATE(
  [Total Sales],
  FILTER(
    VALUES(Customer[CustomerID]),
    [Lifetime Order Count] > 5
  )
)

FILTER lets you build arbitrarily complex filter expressions, including measures-based filters.

KEEPFILTERS — layer instead of replace

By default, a CALCULATE filter REPLACES any existing filter on the same column. KEEPFILTERS makes it intersect with the existing filter. CALCULATE([Sales], Region = 'EA') overrides a Region slicer; CALCULATE([Sales], KEEPFILTERS(Region = 'EA')) only shows EA sales if the slicer already includes EA. Subtle, important. Most DAX 'why doesn't my slicer work' problems are KEEPFILTERS-shaped.

The four most common DAX patterns built on CALCULATE

% of total: [Measure] / CALCULATE([Measure], ALL(Table))
Conditional sum: CALCULATE([Measure], Table[Column] = value)
Same period last year: CALCULATE([Measure], SAMEPERIODLASTYEAR(Date[Date]))
Year-to-date: CALCULATE([Measure], DATESYTD(Date[Date]))

Exercise

In a Power BI report with Sales and Customer tables, write three measures using CALCULATE: (1) Enterprise customer revenue; (2) Revenue as a percentage of grand total regardless of slicers; (3) Revenue from this year (using a Date dimension). Slice the report by Region. Which measures respond? Which don't? What does that tell you about filter context modification?