FILTER DAX Function

Returns a table that represents a subset of another table or expression. Its comes under Filter DAX functions category.

Syntax:

FILTER(<table>, <filter>)

Description:

table -  The table to be filtered. The table can also be an expression that results in a table.
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.

So, Let’s start with an example,  you can download the sample Dataset from below link:-

SuperStoreUS-2015.xlxs

Filter DAX with different-different scenario:

Calculate sum of sales only for Furniture Category:-

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

1. Filter with SUM =

CALCULATE(SUM(Orders[Sales]), 

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

Output : 660,704,31., Refer image 1.

2. Filter with SUMX =

SUMX(

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

Orders[Sales])

Output : 660,704,31., Refer image 1.

3. Filter with OR cond. =

SUMX(

Filter(Orders,

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

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

Orders[Sales]

 )

Image 1:- 

Filter with AND condition:

Filter with AND cond. =
 
SUMX(
 
Filter(Orders,
 
AND(Orders[Product Category]="Furniture",
 
Orders[Region]="East" )),
 
Orders[Sales]
 
)

AND OR Condition with Filter DAX:

1. Filter with AND cond. 2 =

SUMX( 

Filter(Orders,

Orders[Product Category]="Furniture" && 

Orders[Region]="East" ),

Orders[Sales]

)

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