FILTER DAX Function in Power BI

The FILTER dax function returns a table containing only the filtered rows. We can use FILTER to reduce the number of rows in the table that we are working with, and use only specific data in calculations. Filter function creates a virtual table when performing the calculation, and can also be used to create a calculated table.

DAX Syntax FILTER(Table, Filter)

The function has the following parameters:

Example 1: The Filter function can be used to create a calculated table. Let’s create a calculated table using DAX.

FILTER dax function in Power BI

DAX

Filtered Table = FILTER('Sheet1','Sheet1'[10th Marks]>400)

The output of the above dax function is shown below:

FILTER dax function in Power BI

Example 2: Let’s create a measure in power bi and specify only the filtered table with country value “Australia”.

DAX

Calculate with Filter Measure = CALCULATE(SUM(Sheet1[10th Marks]), 
FILTER(Sheet1,
Sheet1[Country]="Australia")
) 

The FILTER function returns the rows from the Sheet1 table, where the Country column value is Australia. And then calculate the sum of the 10th Marks column of the resultant table.

Let’s add the measure in the table. We can see the data that has the Country value Australia.

FILTER dax function in Power BI

Just add the Name field to the table.

FILTER dax function in Power BI

Let’s create another measure with filter-expression instead of FILTER function in DAX.

DAX

Calculate with Filter Expression Measure = CALCULATE(Sum(Sheet1[10th Marks]), Sheet1[Country]="Australia")

The output of the above dax function is shown below:

FILTER dax function in Power BI

Here, there are following findings:

Let’s add the Name column in the table visual again.

FILTER dax function in Power BI

Let’s modify the DAX formula:

DAX

Calculate with Filter Expression Measure = CALCULATE(Sum(Sheet1[10th Marks]), Sheet1[Country]="Australia" || Sheet1[Name]="Akshay")

When I added the Name column also in the filter expression, then the CALCULATE function does not respect the initial filter context from Name column.

FILTER dax function in Power BI