TOP DAX function for everyday use - Filter Functions

[vojtechsima]

Filter functions

The second most important group consists of filter functions, which allow you to filter aggregated values based on an expression or modify the context in which those values are evaluated.

CALCULATE

CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])

Evaluates an expression in a modified filter context.

For me, CALCULATE is the #1 function. If your model is well-structured, you can create something truly elegant with it—almost like a piece of art. It’s simple and powerful, as long as you don’t overthink it (or underthink it?)

CALCULATE provides an elegant way to apply filters, but it works best if you follow certain rules: filter columns, not tables, and try to avoid using FILTER inside the filter expression of CALCULATE.

The most straightforward way to use CALCULATE is by writing 'Table'[Column] = "xx", but that’s just scratching the surface. One of its most powerful features is the ability to modify the filter context using a wide range of additional functions (I’ll cover some of them). This allows you to activate inactive relationships, "ignore" filters from inside or outside a query, or leverage powerful Time Intelligence functions.

REMOVEFILTERS

REMOVEFILTERS([<table> | <column>[, <column>[, <column>[,…]]]])

Clear filters from the specified tables or columns.

Mostly used in CALCULATE filter expressions, REMOVEFILTERS removes filters—simple, right? This function is particularly powerful when you want to ensure that certain calculations remain unaffected by specific filters. You can use it to clear filters from an entire table or just a particular column.

This comes in handy when calculating a portion of a whole. For instance, if you have a dataset with product categories and colors, and you want to determine what percentage each color contributes within its category, you can ignore the color filter as a base and then perform the rest of the calculation. This approach helps derive the percentage contribution of each color within a category.

REMOVEFILTERS is similar to the ALL function. However, when used inside CALCULATE, REMOVEFILTERS is generally recommended, as it tends to perform better and avoids potential side effects.

ALL

ALL( [<table> | <column>[, <column>[, <column>[,…]]]] )

Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.

Mostly used in CALCULATE’s filter expressions, this bad boy can sometimes be a real head-scratcher—especially when you start learning about ALLSELECTED.

But here’s the key to understanding it: whatever you put inside ALL, you’ll get all rows of that. Or, thinking about it differently—you’re ignoring all filters that could be applied to it.

If you want a truly alternative way to think about ALL, imagine it as that one uncle at family gatherings who has absolutely no filter—he’ll say anything, no matter what. Just like him, ALL ignores any filters and brings everything to the table.

ALLSELECTED

ALLSELECTED([<tableName> | <columnName>[, <columnName>[,…]]] )

Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.

Mostly used in CALCULATE’s filter expressions, ALLSELECTED works very similarly as ALL. The only difference is that you are cosplaying ALL just inside the query. But external filters will still affect you. Inside the query means inside visualization where you put the measure it. I think this deserves a picture:

The first table shows a regular SUM. The second table uses the same SUM, but with the ALLSELECTED modifier.

In the second visual (inside the query), individual rows are ignored—you can see that A is normally 100 and B is 200, but here, both display as 300. This happens because ALLSELECTED doesn’t respect the filter context at the row level inside the query.

However, the external query (the slicer on the right) still applies a filter to the visual. In this case, it filters A and B, meaning the external query is respected. As a result, for each selection, you get the aggregated value of both letters.

Since I love unhinged explanations, imagine ALLSELECTED as a group of teenagers. At home, they completely ignore whatever their mom tells them (internal filters). But the moment Rebecca from the other class says something (external filters), they pay full attention—because she’s the famous one, and obviously, you have to follow her trends.

USERELATIONSHIP

USERELATIONSHIP([columnName1],[columnName2])

Specifies the relationship to be used in a specific calculation as the one that exists between columnName1 and columnName2.

Technically a relationship function, but most commonly used in CALCULATE’s filter expressions.

This function is often overlooked at the beginner stage, but once you start using it, it can save you a ton of time in Power BI modeling. Instead of juggling multiple active relationships, you can focus on a single primary active relationship and create multiple inactive ones (often for date fields, but not limited to them).

With this approach, you can easily switch between relationships within a

measure, allowing you to display two different measures in the same visualization—each affected by a different date field (as long as you have a calendar table).

KEEPFILTERS

KEEPFILTERS(<expression>)

Modifies how filters are applied while evaluating a CALCULATE or CALCULATETABLE function.

Used in CALCULATE’s filter expressions, KEEPFILTERS wraps a filter expression and is primarily used to correct unwanted behaviour or address functions that might produce visually confusing results.

Let’s look at an example:

In this example, sum_a is hardcoded to filter for the letter "A". Under standard behaviour, other letters (B, C, etc.) would be filtered by both "A" and their respective letter. Since this is logically impossible, the filter on "A" takes precedence, causing the measure to always return the sum of "A", making it appear as if the value is being repeated across all rows.

On the other hand, sum_a_keepsfilters applies the same filter but wraps it inside KEEPFILTERS. This ensures that the existing "row-level" filters are respected, meaning the measure will only return a value where the filter logically applies—showing a value only for "A" and keeping all other letters blank.

sum_a_keepsfilters =

CALCULATE(

    [sum],

    KEEPFILTERS('dimension'[dimension] = "a")

)

Without using the KEEPFILTERS, formula would look like this:

sum_a =

CALCULATE(

    [sum],

    'dimension'[dimension] = "a"

)

SELECTEDVALUE

SELECTEDVALUE([columnName],[, <alternateResult>])

Returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult.

SELECTEDVALUE is often used in expressions where you need to evaluate the current value against other values. It’s also commonly used as a variable within measures.

Keep in mind that if there’s more than one value for the column in the given context, SELECTEDVALUE will return BLANK—unless you specify an alternative result.