DAX-Lesson 3. FILTER data selection function

In this article, we will look at another important function, which is called FILTER and is used in conjunction with the CALCULATE function. The DAX FILTER function does the same thing as we do when we try to filter data in a regular table by the values in a certain column. That is, in other words, this function reduces the size of the table with which, in turn, the CALCULATE function will work.

Syntax of the function FILTER()

     =FILTER(<table>, <filter>)

<table>

The table we want to filter. Also, there can be an expression that returns a table as a result.

1. 'TableName'
2. ALL('TableName') or ALL('Table Name'[Column Name])
3. FILTER('TableName', 'TableName' [Column Name] = "Green")
4. VALUES('TableName'[ColumnName])

<filter>

A comparison expression that is executed for each row of the table. Conditions for selecting values are specified here.

1. [ColumnName] = “Auto”
2. [ColumnName] >= 6

Let's consider how it looks in practice. Suppose we have a table called Demo:

We need to count the number of records for the product Bikes (bicycles) and display the result in a summary table. To do this, write down the following formula:

=CALCULATE(COUNTROWS(Demo);FILTER(Demo;Demo[Product]="Bikes"))

As a result, we will receive the following values in the field Count_All_Product:

What did the FILTER function do? She reduced the size of the table, as we do in Excel, by setting a filter on the Product column and filtering only records with values Bikes:

Then counted the number of all rows in the reduced table. We see that there are no entries in the Count_All_Product field in the other rows, because the new table does not have skates (Skates) and snowboards (Snow Board).

Related Articles: