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
- [Region] = "India"
- [Income] > [Avg Income]
- [Region] in {"India", "China"}

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

Let’s have a following table in Power BI named Sheet1.

DAX

Filtered Table = 
FILTER(
    'Sheet1',                -- Source table
    'Sheet1'[10th Marks] > 400 -- Condition: only include rows where 10th Marks > 400
)

The output of the above dax function is shown below:

Example: 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]),  -- Expression: sum of 10th Marks
    FILTER(
        Sheet1,               -- Table to filter
        Sheet1[Country] = "Australia" -- Condition: only where 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.

Note: Here, FILTER(Sheet1, ...) respects existing filters on Sheet1. When we add the ALL function which is used to remove the initial filter context, FILTER(ALL(Sheet1), ...) so, it ignores any filters on the table named Sheet1 applied by any column including Country and then we apply the Country filter on the table to evaluate the measure result.

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

Calculate with Filter Expression Measure = 
CALCULATE(
    SUM(Sheet1[10th Marks]),   -- Expression: Sum of 10th Marks
    Sheet1[Country] = "Australia"  -- Filter Expression: Only where Country = "Australia"
)

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.

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.

Let’s modify the DAX formula:

DAX

Calculate with Filter Expression Measure = 
CALCULATE(
    SUM(Sheet1[10th Marks]),        -- Expression: Sum of 10th Marks
    Sheet1[Country] = "Australia"    -- Filter 1: Country is Australia
        ||                           -- OR
    Sheet1[Name] = "Akshay"           -- Filter 2: Name is Akshay
)

Now the output of the above code is shown below:

Implementing the NOT IN Syntax in the FILTER Function in DAX

One common scenario is when we need to exclude certain values from a column or table, and in this case, we can use the NOT IN-like syntax to exclude multiple values from the filter context.

Example: Excluding Specific Countries from a Calculation

Consider the following measure:

DAX

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

Now the output of the above code is shown below:

What Happens in the Measure: This measure calculates the sum of 10th Marks for all rows in the Sheet1 table, excluding any rows where the Country is either "Australia" or "Canada". This is useful when you need to exclude certain countries (or other values) from your aggregation and focus on the remaining data.

Note: While DAX does not directly support the NOT IN syntax, the combination of NOT and IN achieves the same result. The expression NOT(Sheet1[Country] IN {"Australia", "Canada"}) is equivalent to a NOT IN clause in SQL or other query languages.

Previous Next