Everything About DAX Filter in Power BI: 3 Types With Useful Tips

Power BI offers data filtering through the use of DAX filter functions apart from the visualization. Part of the DAX function library, the DAX filter function is an iterator function that generates filtered tables for your data models. Every row that satisfies your condition gets returned as an output.

In this guide, we will explain the basics of Power BI DAX filter functions—their syntax, use cases, types, and applications under multiple conditions. We’ll also present one uncommon but interesting method to use DAX filter functions, i.e., using nested formats.

What are Power BI DAX Filter Functions?

DAX filter functions are a subset of DAX library functions. These functions filter a table based on user-specified conditions. They act very similar to filter functions in Microsoft Excel and help narrow your data and display meaningful information.

Filter function Power BI operations can range from basic segmentation to complex filtering that involves multiple conditions. To help you understand how Power DAX filter functions work, we have discussed separate examples in the upcoming sections. But first, let us have a look at how DAX filter functions are structured.

DAX Filter Function Syntax

The DAX filter function is pretty simple to understand and use. It is a simple command that starts with FILTER and takes in two parameters:

FILTER ( <Table>, <FilterExpression> )

The parameters to be included in the Power BI DAX filter function are as follows:

• <Table> = The name of the table that needs to be filtered. This parameter can also contain an expression that results in a table.
• <FilterExpression> = Filter DAX Power BI condition that is to be evaluated for each row of the given table. The rows for which the condition comes TRUE are retained, while the rest are removed.

Upon the application of the filter function, the Power BI DAX filter function outputs user-given data in the form of a table that contains only the filtered rows.

What are DAX Filter Functions Used For?

You can use filter DAX Power BI functions in a variety of scenarios, like when you want to:

• Reduce the number of rows in a table.
• Hide irrelevant columns or rows.
• Perform arithmetic operations on predefined fields.
• Calculate new parameters or new measures.
• Create dynamic calculations.
• Manipulate data context, and so on. 

List of DAX Filter Functions

Here’s a complete list of supported Power BI DAX filter functions as documented on the Microsoft Power BI website:

FUNCTION	DESCRIPTION
ALL	Returns all rows in a table, or all values in a column, disregarding any applied filters.
ALLCROSSFILTERED	Clears all applied filters to the specified table.
ALLEXCEPT	This filter DAX Power BI returns all the rows in a table except for those that are affected by the specified column filters.
ALLNOBLANKROW	Returns all the rows (except the blank row) in a table, or all the values in a column, disregarding any context filters.
ALLSELECTED	Removes context filters from columns and rows in the current query while retaining all other context filters or explicit filters.
CALCULATE	Evaluates an expression in a modified filter context.
CALCULATETABLE	Evaluates a table expression in a context modified by filters.
FILTER	Returns a table that has been filtered.
KEEPFILTERS	Modifies how filters are applied while evaluating a CALCULATE or CALCULATETABLE function.
LOOKUPVALUE	Fetches a user’s requested value from the table.
REMOVEFILTERS	DAX filter function to clear applied/used filters on user-specified tables or columns.
SELECTEDVALUE	Returns the value when there’s only one value in the specified column, otherwise returns the alternate result.

Example

Let’s take as an example that you have to create a report on Internet Sales outside the US by using a measure that filters and slices out sales in the US according to calendar year and product categories. For this, you first filter the Internet sales USD table by using sales territory and then apply SUMX function to the filtered table. 

FILTER('InternetSales_USD', RELATED('SalesTerritory'[SalesTerritoryCountry])<>"United States")

This expression returns a table which is a subset of Internet sales minus all the rows that belong to the United States sales territory.  The Territory key in the Internet Sales table is linked to SalesTerritoryCountry in the SalesTerritory table through RELATED function

The table below demonstrates the concept for the measure, NON USA Internet Sales. This is achieved by following code:

SUMX(FILTER('InternetSales_USD', RELATED('SalesTerritory'[SalesTerritoryCountry])<>"United States")  
     ,'InternetSales_USD'[SalesAmount_USD])

The table compares all Internet sales with non- USA Internet sales  by excluding United States sales from the computation.This shows that the filter expression works.

In order to re-create this table, you can add the field, SalesTerritoryCountry, to the Row Labels area of a report or PivotTable.

The final report table represents the results when you create a PivotTable by using the measure, NON USA Internet Sales. Add the field, CalendarYear, to the Row Labels area of the PivotTable and add the field, ProductCategoryName, to the Column Labels area.

DAX Filter Functions in Action

In this section of filter function Power BI, we present to you the many ways you can use DAX filter functions for your dataset in Power BI. 

• DAX Filter Function as a Table Expression.
• DAX Filter Function With Multiple Conditions.
• Using Filter Function in a Measure.

DAX Filter Function as a Table Expression

Consider this simple example to get started with the DAX filter function. The sample Excel workbook file used in this tutorial can be found here- FinancialSample. 

In this example, we have created a subset of our financial sample data table, named Subset 1 Financial Data. This table uses the DAX filter function to isolate Carretera products out of the 6 available; Amarilla, Carretera, Montana, Paseo, VTT, and Velo. Here’s the expression for our Power BI DAX filter function: 

Subset 1 Financial Data =
FILTER ( FinancialSample, FinancialSample[Product] = "Carretera" 

Please note that DAX filter functions aren’t case-sensitive. This means that FinancialSample[Product] = ‘carretera’ and FinancialSample[Product] = ‘Carretera’ mean the same. For finding case-sensitive data entries, we recommend using FIND() or EXACT() DAX functions.

DAX Filter Function With Multiple Conditions

Extending on our previous example, suppose we want to impose two or more conditions on our financial sample dataset. Let’s assume we wish to know details around all Carretera products sold in the United States of America. 

Filter DAX Power BI allows users to enforce multiple conditions in their filter expressions using AND (equivalent syntax- &&) and OR (equivalent syntax- ||). This is what our new DAX filter function AND expression would look like:

Subset 2 Financial Data =
FILTER (
    FinancialSample,
    FinancialSample[Product] = "Carretera"
        && FinancialSample[Country] = "United States"
)

The  && in the code represents AND expression. We’ve created a new filter table called Subset 2 Financial Data in the above operation. This new filtered table will be visible in your Fields Pane.

Another example could be when we wish to find details around the product Montana that has been either sold in Canada or in the United States of America. Our new OR expression, in this case, would look like:

Subset 3 Financial Data =
FILTER (
    FinancialSample,
    FinancialSample[Product] = "Montana"
        && ( FinancialSample[Country] = "United States"
        || FinancialSample[Country] = "Canada" )
)

Using Filter Function in a Measure 

It’s more usual to see and use DAX filter functions inside a measure. Using a tabular function within a measure allows you to dynamically build virtual tables based on the filter requirements in the table visualizations. Here’s an example to enlighten what is being said:

Sales of Montana in Canada Financial Data =
VAR filtered =
    FILTER (
        FinancialSample,
        FinancialSample[Product] = "Montana"
            || FinancialSample[Country] = "Carretera"
    )
RETURN
    CALCULATE ( [Sales], filtered )

In this example, we are creating a variable called filtered, which contains data for the product Montana and Carretera (specified using OR “||” expression). This variable is then passed onto another function called CALCULATE which returns sales numbers for each of these products in a separate column.

Filter Function Doesn’t Change Your Columns

By default, the filter function doesn’t change your data table columns. If you prefer to only see columns that are being affected or chosen few as per your requirements, instead of all, you can do that by using the filter function as an input function for another function.

Sales of Montana in Canada Financial Data =
VAR filtered =
    FILTER (
        FinancialSample,
        FinancialSample[Product] = "Montana"
            && FinancialSample[Country] = "Canada"
    )
RETURN
    SELECTCOLUMNS ( filtered, “Montana_Sales”, FinancialSample[Sales] )

This filter expression returns sales values for the product, Montana, in the country Canada into a separate table titled Montana_Sales.

Nesting DAX Filter Functions

There’s another way using which you can combine your data segregation conditions, and that happens using nested filter functions. A nested function is like a loop. The output of one function becomes the input for the other.

Suppose you want to know the high discount bands offered for the product Carretera. You can either do so, by using AND “&&” function or by using the nested structure of DAX filter functions like this:

Carretera High Discount Bands =
FILTER (
    FILTER ( FinancialSample, FinancialSample[Product] = "Carretera" ),
    FinancialSample[Discount] = "High"
)

With this, I hope we have enhanced your understanding and clarified doubts around using Power BI DAX filter functions. Discover more about DAX functions, their syntax, types, and uses.

Common Pitfalls and Best Practices

Pitfalls to Avoid

1. Context Confusion: Misunderstanding row and filter contexts can lead to incorrect results.
2. Excessive Nesting: Overusing nested functions impacts readability and performance.
3. Performance Issues: Careless use of ALL or REMOVEFILTERS can slow down calculations.
4. Syntax Errors: Small mistakes, like missing parentheses, can break formulas.

Best Practices

1. Understand Context: Master row and filter contexts to write accurate formulas.
2. Use Variables: Simplify complex formulas and improve performance.
3. Simplify Logic: Use CALCULATETABLE or SUMMARIZE to avoid unnecessary nesting.
4. Optimize Filters: Use ALL or REMOVEFILTERS sparingly to maintain performance.

These tips will help you avoid common mistakes and create more efficient DAX formulas.

Conclusion

The Filter Function in Power BI is a simple Power BI function that allows you to iterate down the rows of any table, creating a row context for each and testing whether the row should be included in your calculation. The ones that qualify as true are aggregated as a separate table and given to the user as an output. In this tutorial, we looked at ways to create conditions using AND “&&” and OR “||” expressions. Lastly, we presented one more way to combine multiple conditions – using nested structures.

While Power BI supports data ingestion from multiple sources, why not streamline your data uploads using a No-Code and Zero Data Loss Solution like Hevo?

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready

Why not try Hevo and the action for yourself? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also check out our Hevo pricing plans to choose the plan that best matches your business needs.

FAQs