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:

• 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. For example, [Amount] > 0 or [Region] = "France"

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

DAX

The output of the above dax function is shown below:

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.

Just add the Name field to the table.

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

DAX

The output of the above dax function is shown below:

Here, there are following findings:

• The CALCULATE with FILTER function respects the initial filter context.
• The CALCULATE function with filter expression instead of FILTER function overrides the initial filter context on the columns used in filter expression.

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

Let’s modify the DAX formula:

DAX

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.