FILTER and ALL Functions in DAX

FILTER Function

Sometimes you need to apply more advanced filters. The FILTER function allows you to do that. So you might winder why we need a filter function if we can filter data using CALCULATE. Well it turns out CALCULATE is designed to be fast, as it's what you're going to use by default and most often. In order to guarantee that a filter is fast, the designers had to make it limited as well. To get the more complicated examples, we need to use the FILTER function.

• CALCULATE is designed to be fast.
• FILTER is designed to be flexible.

Let's take a look at where we would use that function.

When to use FILTER?

There are a couple of specific scenarios. First, whenever you want to filter on expression involving multiple columns or any kind of complex calculation, you need to use FILTER. CALCULATE by itself only lets you compare a single column to a fixed value.
The second area when you need to use filter is when you want to compare an aggregate function or measure. Let's say you want to filter products that have a price that above average, given all of the products. Basic filtering and CALCULATE won't let you do that because that average needs to be dynamically calculated looking at all the data. To do that you need to USE FILTER.
Finally, for more advanced techniques, sometimes you need to return a table of filtered rows. That's what the FILTER function actually does, it returns a table. Now why might you want to do that? Well there are some functions that require a table as an input parameter.

• Evaluating on multiple columns
• Comparing to an aggregate function or measure
• Needing to return a table

Example of Filtering on Multiple Columns:

So let's take a look at how we can use the FILTER function.

Measure Name = CALCULATE( SUM( Total ), FILTER( Sales, QTY * Price < 100> ) )

The FILTER function requires two parameters. The first is the table we want to apply the filer to Sales. The second parameter is the expression we want to use QTY * Price < 100. Putting it all together, the above expression will filter all of the entries in the sales table to those rows that have a gross sales of less than $100.

ALL Function

One of the things that is different with DAX compared to Excel formulas or SQL is it's really easy to unapply filters. We can do this using the ALL function. This allows us to unapply filters on a specific column or an entire table.

Using ALL

So as an example, let's say that you want to have a measure that shows total sales for the current context, but in fact is applied to all historical data, even if the user has filtered down to a specific year. This Measure can be used as a comparison point of regular sales. So as an example, let's say that in our current filter context, based on the layout of our pivot table or grid or visual, we've sold $200 worth of green widgets in Southeast Asia. And you say, okay, well that seems pretty low. Let's see what our total sales have been historically for all time, and it turns out that historically all of your total sales have been $400 of green widgets in Southeast Asia. And so compared to that total, this year's sales are quit high. So it shows that it's a growing market. So to do this kind of analysis, we can use the ALL function.

• HIstorical Analysis: You want to compare to historical sales.
• ALL() Removes Filters: You can ignore and remove any filters on your data table.

Example of Clearing Filters on the DATE Table:

The ALL function can either be applied to a specific column or it can be applied to an entire table. In this example we're going to apply this to the date table. A date table is used manage date based manipulations. The date table is where the user is filtering dates.

Historical Sales = CALCULATE( SUM( [Total] ), ALL( 'Date' ) )

This example is very similar to the one with the FILTER function. The difference is that we're using the ALL function on the Date table. Here you can see in the second parameter to CALCULATE is the ALL function. This function clears any filters in the entire date table, including any of its columns.