Home » DAX » DAX – Filter Function

DAX – Filter Function

Returns a table representing a subset of another table or expression. It falls under the category of Filter DAX functions.

Syntax:

`FILTER(<table>, <filter>)`

Description:

 S no. Parameter Description 1 table The table to be filtered. The table can also be an expression that results in a table. 2 filter A Boolean expression that is to be evaluated for each row of the table.

Note:

1. FILTER is DAX function used to summarize the data with define criteria’s.
2. You can use FILTER to reduce the number of rows in the table that you are working with, and use only specific data in calculations.
3. Filter returns a table containing only the filtered rows.

Filter DAX with different different scenario:

Calculate sum of sales only for Furniture Category:-

Here, the filter function filters the rows only for the “Furniture” category and returns the table. Then, the sum function will calculate the summation of the sales values.

```Filter with SUM =

CALCULATE(SUM(Orders[Sales]),

Filter(Orders, Orders[Product Category]="Furniture"))

Output : 660,704,31., Refer image 1.```
```Filter with SUMX =

SUMX(

Filter(Orders, Orders[Product Category]="Furniture"),

Orders[Sales])

Output : 660,704,31., Refer image 1.```
```Filter with OR cond. =

SUMX(

Filter(Orders,

OR(Orders[Product Category]="Furniture",

Orders[Product Category]="Office Supplies" )),

Orders[Sales]

)

Output : Refer image 1.```

Image 1 – filter conditions output

Filter with AND condition:

```Filter with AND cond. =

SUMX(

Filter(Orders,

AND(Orders[Product Category]="Furniture",

Orders[Region]="East" )),

Orders[Sales]

)```

Filter with AND condition

Different ways to write AND OR Conditions with Filter:-

Filter with AND OR Condition

```Filter with AND cond. 2 =

SUMX(

Filter(Orders,

Orders[Product Category]="Furniture" &&

Orders[Region]="East" ),

Orders[Sales]

)

```
```Filter with OR cond. 2 =

SUMX(

Filter(Orders,

Orders[Product Category]="Furniture" ||

Orders[Region]="East" ),

Orders[Sales]

)```

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our , we will revert to you asap.