Graphed LogoGraphed
Login

How to Filter a Measure in Power BI DAX

Cody Schneider•

Building dynamic reports in Power BI often comes down to one essential skill: filtering a measure using DAX. While a simple sum or average is useful, the real power comes from creating calculations that respond to specific conditions, like calculating sales for only a specific region or for last year. This article will walk you through the core concepts and functions you need to master, starting with the most important DAX function of all: CALCULATE.

What is Filter Context and Why Does It Matter?

Before writing a single line of DAX to filter a measure, you need to understand the concept of "filter context." In simple terms, filter context is the set of active filters being applied to your data at any given point in your report.

Imagine you have a sales table and you create a table visual showing "Sales Amount" by "Country."

  • When Power BI calculates the grand total for the "Sales Amount" column, the filter context is empty. It's looking at the entire table.

  • When it calculates the sales amount for a specific row in the visual, say for "Canada," the filter context is 'Sales'[Country] = "Canada".

  • If you add a slicer for "Year" and select "2023," the filter context for the "Canada" row now becomes 'Sales'[Country] = "Canada" AND 'Date'[Year] = 2023.

Every cell in a Power BI visual has a unique filter context based on its row, column, and any active slicers or report-level filters. Learning to manipulate this filter context is the secret to powerful DAX measures.

The Main Tool for Filtering: The CALCULATE Function

If you take away just one thing about filtering measures in DAX, let it be the CALCULATE function. It is arguably the most important function in the entire DAX library. Its primary job is to modify the filter context for a calculation.

The basic syntax for CALCULATE is:

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

  • <,expression>: The calculation you want to perform, which is usually a measure (like SUM(Sales[Profit])).

  • <,filter1>, <,filter2>, ...: The filters you want to apply. These filters will override any existing conflicting filters in the current context.

Step-by-Step Example: Calculating Sales for a Specific Product Category

Let's walk through a common scenario. You have a table showing total sales by product category, but you also want a specific measure that always shows the sales for just the "Accessories" category, regardless of what's selected.

Step 1: Create a Base Measure

First, it's a best practice to create a simple, reusable base measure for your core metrics. In this case, let's create one for total sales.

Total Sales = SUM(Sales[Revenue])

This measure, when added to a visual, will respect the natural filter context. For the "Bikes" row, it shows bike sales, for the "Clothing" row, it shows clothing sales.

Step 2: Create the Filtered Measure Using CALCULATE

Now, let's create our new measure that is specifically for the "Accessories" category.

Accessories Sales = CALCULATE( [Total Sales], 'Product'[Category] = "Accessories" )

Step 3: Add Both Measures to a Visual

When you add both [Total Sales] and [Accessories Sales] to a table visual that has product categories on the rows, you’ll see:

  • The [Total Sales] column shows different values for each category (bikes, clothing, accessories).

  • The [Accessories Sales] column shows the same value in every row—the total sales for accessories.

This is CALCULATE in action. For each row of the visual, it takes our expression [Total Sales] and applies a new filter: 'Product'[Category] = "Accessories". This new filter overrides the filter context coming from the visual's row. So, even on the "Bikes" row, the formula tells Power BI: "Forget that this is the bike row for a second, and calculate the total sales for accessories instead."

Going Deeper: Using Other Functions Inside CALCULATE

Applying a simple filter like 'Table'[Column] = "Value" is just the beginning. The real flexibility comes from combining CALCULATE with other functions that allow for more complex filtering logic.

Using the FILTER Function for Complex Conditions

Sometimes you need to apply a filter condition that's more complicated than a simple value check. This is where the FILTER function is useful. FILTER iterates through a table row by row and creates a smaller, virtual table containing only the rows that meet your condition.

Let's say you want to calculate the sales for "high-ticket" items, which you define as any transaction where the quantity sold was more than 3 units.

High Quantity Sales = CALCULATE( [Total Sales], FILTER( 'Sales', 'Sales'[OrderQuantity] > 3 ) )

Here's the breakdown:

  1. FILTER('Sales', 'Sales'[OrderQuantity] > 3) goes through the entire Sales table and creates a temporary table that only includes rows where the order quantity was greater than 3.

  2. CALCULATE then computes [Total Sales] using this new temporary table as its filter context.

You use FILTER when you need to perform row-level evaluations, such as comparing two columns or filtering based on a measure's result.

Removing Filters with ALL, ALLEXCEPT, and ALLSELECTED

Just as powerful as adding filters is the ability to remove them. This is essential for calculations like percentages of a grand total.

1. Using ALL to Calculate Percent of Grand Total

If you want to calculate what percentage of the total sales each product category represents, you need to divide the sales for that category by the grand total sales of all categories. The ALL function can be used to remove filters from a column or an entire table.

Here’s how you'd write the measure:

% of Total Category Sales = DIVIDE( [Total Sales], CALCULATE( [Total Sales], ALL('Product'[Category]) ) )

Let’s analyze the denominator. In a visual with product categories on the rows, the CALCULATE(..., ALL('Product'[Category])) part tells Power BI: "For this calculation, ignore any filters coming from the 'Product'[Category]' column." This means for every row in the visual, the denominator will always be the grand total, allowing you to correctly calculate the percentage.

2. Using ALLEXCEPT to Keep Certain Filters

What if you want to remove all filters from a table except for one or two columns? That's what ALLEXCEPT is for. A common use case is calculating the sales for a product's model, respecting the year filter but ignoring any regional filters.

Model Sales Across All Regions = CALCULATE( [Total Sales], ALLEXCEPT( 'Sales', 'Date'[CalendarYear], 'Product'[ModelName] ) )

This measure would ignore filters from columns like Country, State, or City, but it will still be filtered by the calendar year and the model name coming from the visual's context.

3. Using ALLSELECTED for Slicer-Aware Calculations

ALLSELECTED is a bit trickier but incredibly useful. It removes the filters from the context inside a visual (like from the rows of a table) but respects any filters coming from slicers or other visuals.

Imagine a scenario where the user can select multiple product categories in a slicer. You want to calculate each category's percentage of the total for only the selected categories, not of the grand total of all categories.

% of Selected Total Sales = DIVIDE( [Total Sales], CALCULATE( [Total Sales], ALLSELECTED('Product'[Category]) ) )

If a user selects "Bikes" and "Clothing" in a slicer, the denominator of this measure will be the sum of sales for just Bikes and Clothing, making the percentages add up to 100% within the user's current selection.

A Practical Time Intelligence Example

Much of DAX's time intelligence capabilities are just shorthand for more complex CALCULATE logic. For example, let's say you want to calculate sales from the previous year. You might write something like:

Sales Last Year = CALCULATE( [Total Sales], SAMEPERIODLASTYEAR('Date'[Date]) )

Behind the scenes, SAMEPERIODLASTYEAR('Date'[Date]) is a "syntactic sugar" function. It's essentially creating a table of dates from the previous year and using that table to filter the [Total Sales] measure. It's yet another example of CALCULATE modifying the filter context to get you the exact slice of data you need.

Final Thoughts

Mastering how to filter measures is a fundamental Power BI skill that transforms your reports from static displays into interactive analytical tools. It all revolves around understanding filter context and using CALCULATE to manipulate it. By combining CALCULATE with other functions like FILTER and ALL, you unlock the ability to answer almost any business question your underlying data holds.

Learning the fine points of DAX and its functions takes time and practice. One of our main goals with Graphed was to eliminate this steep learning curve. Instead of needing to code complex measures, you can connect your data sources and simply ask questions in plain English—like "what were my accessory sales last year compared to this year?"—and we instantly build the correct visualizations and underlying calculations for you. It allows you and your team to focus on the insights, not on the complexity of writing code.