For Power BI version 2.76.5678.782 (December 2019).

FILTER() is a function that filters a table and returns the remaining rows. It is similar to function CALCULATE(). The difference is FILTER() returns a filtered table and CALCULATE() returns value of the expression for the filtered table. CALCULATE() is used with simple filters, for example comparing values in the column to a specified value. We commonly use FILTER() function in the CALCULATE() function to create complex filters.

You can find more on CALCULATE() function in article DAX function CALCULATE.

Syntax

FILTER (Table, Filter)

Example: filtering a table

We have current storage of office supplies in our Supplies table.

We want to filter out Paper clips. We follow the steps bellow.

1. We don’t want to create a new measure because measures are used to return a single value. Instead, we create a calculated table using New table.

2. We enter the following formula.

FILTER_Example = FILTER(Supplies, Supplies[Name] <> “Paper clips”)

3. We get a filtered table.

Example: counting rows with FILTER() or CALCULATE()

We can count rows using FILTER() and CALCULATE() functions. Let’s again use table of office supplies Supplies. We want to know how many articles we have without the article Paper clips. We create measures using following formulas.

FILTER_Example = COUNTROWS(FILTER(Supplies, Supplies[Name] <> “Paper clips”))

CALCULATE_Example = CALCULATE(COUNTROWS(Supplies), Supplies[Name] <> “Paper clips”)

In both cases formula is executed by the following steps.

1. Table Supplies is filtered, leaving only rows that don’t have value Paper clips in Name column.
2. Rows are counted in the filtered table.

We use Table visualization to check the values measures return. We see the result is the same in both cases.