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.

So, let’s begin with an example. You can download the sample dataset from the link provided.

• SuperStoreUS-2015.xlxs

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.

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.