#### October 30, 2020

## DAX function FILTER

*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)

Parameter | Description |

Table | Table to be filtered |

Filter | 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.

- Table
**Supplies**is filtered, leaving only rows that don’t have value**Paper clips**in**Name**column. - 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.