Sign in or start a free trial to avail of this feature.
6. Applying the Filter Function
Filtering is a very important concept in DAX. In this lesson, we will look at the Filter function, and use it in a Calculate function on a subset of our data
The Filter Function
- FILTER takes a table as an input and produces a subset of that table, based on the filter criteria supplied to it
- FILTER is only used within other functions, such as CALCULATE
- FILTER is not required when creating a CALCULATE function, however, it can help avoid some unexpected results
- Alt B, F – Create measure (from Excel)
Filtering is a key concept in DAX, and one that will feature heavily in the remaining lessons of this course. Filtering functions in DAX can be complex, and go beyond the filtering functionality available in Excel.
Over the next two lessons, we'll carefully look at the most commonly used filtering functions so you can clearly understand how they work. We've seen many of these functions previously, so our focus will be on understanding the concepts behind the functions.
In this lesson, we'll take an in-depth look at the filter function.
We'll start with a premade pivot chart featuring a bar chart of sales by customer, and slicers for customer and product.
We're currently looking at a small sample of six customers, selected using the slicer.
The filter function is used to analyze the subset of a table, based on one or more filters of interest.
It's not used on its own, but instead, as part of another formula, such as calculate.
Like all functions we'll see in these lessons, it's a table function.
This means that we applied the function to a table, and it returns another table, generally of reduced size.
We'll see the filter function in action by creating a measure to calculate the sales for the product, Melacaine.
We'll create a new measure with Alt + b + f, and call it Melacaine Sales.
The measure will use the calculate function.
The expression to calculate will be the sum of sales, and for the filter argument, we'll use the filter function.
Filter takes two arguments.
The first is the dataset, in our case, the Pharma Data table.
The second is the filter expression.
In this case, we want the product name to be equal to Melacaine.
We'll select OK, and add the new measure to our chart.
The measure calculates total sales for the product, Melacaine, for each of the customers.
In this case, we used the filter function to reduce our Pharma Data set to the rows which contain sales of the product, Melacaine.
However, we did not actually have to use filter in this context.
Instead, we could have just added the filter condition as a second argument to the calculate.
While that would have worked here, this can have some unintended consequences, as we'll see in a future lesson.
There are also some situations where you must use the filter function.
In this example, we used the value of the product name column in our filter condition.
If you want to use the value of a measure in your condition, then you must use the filter function, as simply creating the condition will give an error.
In the next lesson, we'll continue our look at filtering functions by focusing on all, all except, values, and distinct.