Power BI DAX CALCULATE Explained

The Power BI DAX CALCULATE Function is one of the most widely used formulas in the platform. It gives developers the ability to escape the standard filter and slicer context applied to a page. Meaning that it allows you to show things like full year sales on a visual even though a dashboard is filtered to only show a single month.

Because the CALCULATE function in DAX escapes the standard filtering context, it’s usually utilized with a FILTER formula that tells Power BI if it ignores the current slicers and filters on the page, how do you want to filter the measure that’s going to be added to a visual? We’ll explain both.

Let’s dive in!

Understanding the CALCULATE DAX Function

The CALCULATE function modifies the context (filters applied) in which a DAX formula is evaluated. It’s usually used to change the context of a calculation for presentation in a specific visual. It’s a way that you can present data that’s not subject to the other filters on a page.

Syntax:

CALCULATE(<expression>, <filter1>, <filter2>, ...)
  • <expression> The DAX formula you want to calculate.
  • <filter1>, <filter2>, … Conditions that define the context of the calculation.

Here’s how you could write a DAX formula to sum sales while removing any filters on the “Category” column:

CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales[Category]))

In this formula:

  • Sales is the name of your table that contains the sales data.
  • [SalesAmount] is the column in your table that contains the sales figures you want to sum.
  • [Category] is the column from which you want to remove any filters.

You’ll notice in the example that we don’t specifically use the word FILTER. This is intentional because in addition to setting a FILTER there are a number of premade filters and filter contexts that exist in DAX that we’ll explain below.

Using CALCULATE with FILTER

Here are some examples to highlight how to use CALCULATE and different FILTERS together to escape the context of the page of a report and present data that otherwise could be filtered with a slicer selection.

Imagine you’re analyzing cookie sales data and want to calculate the total sales for a specific type of cookie, say “Chocolate Chip”.

CALCULATE(
    SUM(CookieSales[SalesAmount]),
    FILTER(
        CookieSales,
        CookieSales[CookieType] = "Chocolate Chip"
    )
)

This formula sums up the sales amount for only “Chocolate Chip” cookies, applying a filter context to the CookieSales table.

Example 2: Sales in a Specific Year with Multiple Conditions

Now, let’s calculate the total sales for “Chocolate Chip” cookies in the year 2023.

CALCULATE(
    SUM(CookieSales[SalesAmount]),
    FILTER(
        CookieSales,
        CookieSales[CookieType] = "Chocolate Chip" &&
        YEAR(CookieSales[SalesDate]) = 2023
    )
)

In this example, the resulting measure will only show Chocolate Chip Cookie Sales during 2023 regardless of how the rest of the page is filtered.

Useful DAX Functions for Filtering in Power BI

In addition to the standard FILTER function, there are additional pre-defined contexts that Microsoft makes available. The following list isn’t entirely inclusive of every option but highlights some of the most common contexts that you will come across when creating CALCULATE functions.

  • FILTER: Returns a table that has been filtered down based on a given expression. This function is versatile, allowing for complex row-level filtering based on dynamic conditions.
  • ALL: Removes all filters from a table or column(s). When used without arguments, it clears all filters from the entire model. It’s useful for calculating aggregates across all data, ignoring any slicers or filters applied.
  • ALLSELECTED: Removes all filters in the context of the calculation except the filters that have been applied directly by the user. It’s particularly useful for maintaining user selections while performing calculations that need to ignore other filters applied through report interactions.
  • ALLEXCEPT: Removes all filters from the specified table except for the filters on the specified columns. This function allows for more granular control, keeping some filters active while ignoring others.

There are also premade functions that will calculate totals over time when have slightly different syntax but are worth being aware of before trying to calculate and filter between two dates using a standard CALCULATE and FILTER combination.

Applying Date Filters with CALCULATE

TOTALYTD, TOTALQTD, TOTALMTD are three pre-defined time intelligence DAX functions that are often used with CALCULATE. These functions calculate aggregates (such as sums or averages) for year-to-date, quarter-to-date, and month-to-date, respectively, adjusting the calculation based on the filters applied to date columns.

Her’s an example of the Syntax:

Total Sales YTD = CALCULATE(TOTALYTD(SUM(Sales[SalesAmount]), Sales[DateColumn]))

In this formula:

  • Sales is the name of your table that contains the sales data.
  • SalesAmount is the column in your table that records the amount of each sale.
  • DateColumn is the column in your “Sales” table that contains the date of each sale.

This formula calculates the total sales amount for the current year up to the last date in the data context. The TOTALYTD function aggregates sales amounts over the year, starting from the beginning of the calendar year up to the current date in your data.

The CALCULATE function is used here to modify the filter context, which allows TOTALYTD to work correctly even if there are other filters applied in the report or on the page.

Conclusion

The CALCULATE function, especially when used with FILTER and understanding different contexts, unlocks a vast potential for dynamic and complex data analysis in Power BI. By mastering these concepts, illustrated through cookie sales data, you can enhance your data models and reports, providing deeper insights and more actionable intelligence for business users. Whether you’re calculating total sales, analyzing trends, or comparing performance across different dimensions, CALCULATE offers the flexibility and power to meet a wide range of analytical needs.

Scroll to Top