Ravindra BagaleCourses & study guides

13. DAX: Data Analysis Expressions

13.8 CALCULATE – The Most Important Function

By Last updated:

CALCULATE(<expression>, <filter1>, <filter2>, …) evaluates an expression in a modified filter context. Filters can add, replace or remove filters.

Blinkit Sales = CALCULATE([Total Sales], Orders[Platform] = "Blinkit")

Amazon Now Sales = CALCULATE([Total Sales], Orders[Platform] = "Amazon Now")

Pune & Nashik Orders = CALCULATE([Total Orders], DarkStore[City] IN { "Pune", "Nashik" })

Blinkit Dairy Sales =
CALCULATE(
    [Total Sales],
    Orders[Platform] = "Blinkit",
    Product[Category] = "Dairy & Breakfast"
)

UPI Orders = CALCULATE([Total Orders], Orders[Payment Mode] = "UPI")

Discounted Sales = CALCULATE([Total Sales], Orders[Discount] > 0)

Key rules:

  1. A simple filter like Orders[Platform] = "Blinkit" replaces any existing filter on that same column. So [Blinkit Sales] shows Blinkit sales even on a row for "Amazon Now".
  2. Multiple filter arguments are combined with AND.
  3. To keep the existing filter and add to it, wrap with KEEPFILTERS: CALCULATE([Total Sales], KEEPFILTERS(Orders[Platform] = "Blinkit")).
  4. Filter modifiers such as ALL, REMOVEFILTERS, USERELATIONSHIP and CROSSFILTER are also used inside CALCULATE.

CALCULATE samajla mhanje DAX cha nimma bhag samajla. Pratyek udaharan swatah type karun bagha.

Ravindra Bagale's Tip

The mistake I see most often with CALCULATE is expecting a filter argument to add to the slicer selection, when it actually replaces the filter on that column. CALCULATE([Total Sales], DarkStore[City] = "Pune") shows Pune even when Nashik is selected. Use KEEPFILTERS when you want to intersect instead. Keep this in mind!