DAX – Filter 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:

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.

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.

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.

Filter with AND condition:

Filter with AND cond. =

SUMX(

Filter(Orders,

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

Orders[Region]="East" )),

Orders[Sales]

)

Different ways to write AND OR Conditions with Filter:-

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.