FILTER DAX Function

The FILTER 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. Actually, Filter function creates a virtual, 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: Let’s create a calculated table using DAX.

DAX

Example 2: Create a measure.

DAX

The above formula returns the result as we want to see only the Country that has the value Australia.

The table filter, filters out the whole table and then evaluate the result where the Country column value is Australia. This operation slows down the process.

Just add the Name field to the table.

Actually, the calculation is done for measure in such a way that we are getting the measure calculation on the basis of country Australia, but context filters affect the measure.

So, for example, for Name Kiara the measure is evaluated in such a way that the Country is India, and Name is Kiara and Country is Australia (as specified in the measure calculation), that’s why the result is blank.

DAX

Here, when we add the ALL function it ignores any filters on the table applied by any column including Country in the table and then we apply the Country filter on the table to evaluate the measure result.

So, for example, for Name Kiara the measure is evaluated in such a way that the Country is Australia (as specified in the measure calculation), and no other filter is applied in the calculation that’s why the result is the sum of the Total Marks of Country Australia.