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:
- FILTER is DAX function used to summarize the data with define criteria’s.
- 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.
- Filter returns a table containing only the filtered rows.
So, let’s begin with an example. You can download the sample dataset from the link provided.
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 contact form , we will revert to you asap.