Try Hevo for free

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 in narrowing your data and displaying 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.

Are you looking for an easy way to make your data analysis-ready? Solve your data replication problems with Hevo’s reliable, no-code, automated pipelines with 150+ connectors.
Get your free trial right away!

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.
DAX Filter Function Syntax
Image Source: Data Bear

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:

FUNCTIONDESCRIPTION
ALLReturns all rows in a table, or all values in a column, disregarding any applied filters.
ALLCROSSFILTEREDClears all applied filters to the specified table.
ALLEXCEPTThis filter DAX Power BI returns all the rows in a table except for those that are affected by the specified column filters.
ALLNOBLANKROWReturns all the rows (except the blank row) in a table, or all the values in a column, disregarding any context filters.
ALLSELECTEDRemoves context filters from columns and rows in the current query while retaining all other context filters or explicit filters.
CALCULATEEvaluates an expression in a modified filter context.
CALCULATETABLEEvaluates a table expression in a context modified by filters.
FILTERReturns a table that has been filtered.
KEEPFILTERSModifies how filters are applied while evaluating a CALCULATE or CALCULATETABLE function.
LOOKUPVALUEFetches a user’s requested value from the table.
REMOVEFILTERSDAX filter function to clear applied/used filters on user-specified tables or columns.
SELECTEDVALUEReturns the value when there’s only one value in the specified column, otherwise returns the alternate result.
Power BI DAX filter functions from Microsoft.

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")
  • The FILTER function is used to create a new table or subset from the 'InternetSales_USD' table based on specific criteria.
  • It checks each row in the 'InternetSales_USD' table against the condition defined.
  • The condition evaluates the SalesTerritoryCountry column from the related 'SalesTerritory' table.
  • It filters out rows where the SalesTerritoryCountry is equal to "United States".
  • The result is a table containing only sales records from territories outside the United States, which can be useful for analyzing international sales.

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 SUMX function calculates the total of a specified expression across a table or a filtered subset.
  • It first filters the 'InternetSales_USD' table to include only the rows where the SalesTerritoryCountry is not "United States".
  • The RELATED function retrieves the country information from the related 'SalesTerritory' table for each row in the sales data.
  • The expression being summed is the 'InternetSales_USD'[SalesAmount_USD], which represents the sales amounts in USD.
  • The final result is the total sales amount from all territories outside the United States, helping analyze international sales performance.
Image Source

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.

Image Source

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

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. 

Financial Sample Data Table: DAX Filter
Financial Sample Data Table, Image Source: Self

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 code creates a new subset of data named “Subset 2 Financial Data” using the FILTER function.
  • It operates on the FinancialSample table, which contains financial records.
  • The filtering conditions specify that only rows where the Product column equals "Carretera" are included.
  • Additionally, it filters for rows where the Country column equals "United States".
  • The result is a filtered table containing only the financial data for the product “Carretera” sold in the 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" )
)
  • The code defines a new subset of data called “Subset 3 Financial Data” using the FILTER function.
  • It filters rows from the FinancialSample table where the Product column equals "Montana".
  • The filtering criteria include rows where the Country column is either "United States" or "Canada".
  • The logical operator || indicates that either country condition can be true for a row to be included.
  • The result is a filtered table that contains financial data for the product “Montana” sold in either the United States or 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 )
  • The code defines a new measure called “Sales of Montana in Canada Financial Data” using a variable (VAR) to hold filtered data.
  • The FILTER function is applied to the FinancialSample table, selecting rows where the Product is “Montana” or the Country is “Carretera.”
  • The RETURN statement uses the CALCULATE function to compute the total sales based on the filtered data.
  • The [Sales] expression inside CALCULATE represents the total sales amount that is being calculated after applying the filter.
  • The result will show the sales figures for the “Montana” product and for the “Carretera” country from the financial data.

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] )
  • The code defines a new measure called “Sales of Montana in Canada Financial Data,” using a variable (VAR) named filtered to store specific data.
  • The FILTER function selects rows from the FinancialSample table where the Product is “Montana” and the Country is “Canada.”
  • The RETURN statement utilizes the SELECTCOLUMNS function to create a new table from the filtered data.
  • The new table includes a single column named “Montana_Sales,” which pulls the Sales values from the FinancialSample table.
  • The result will be a table displaying only the sales figures for the “Montana” product sold in Canada.

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, we hope we have enhanced your understanding and clarified doubts around using Power BI DAX filter functions. 

Recommended Articles

Discover more on DAX functions, their syntax, types, and uses in the following exhaustive guide- Understanding DAX Power BI: A Comprehensive Guide.

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

Visit our Website to Explore Hevo

Why not try Hevo and the action for yourself? Sign Up  here 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 best-matched plan for your business needs. 

Have more ideas or Power BI features you would like us to cover? Drop a comment below to let us know. 

Divyansh Sharma
Marketing Research Analyst, Hevo Data

Divyansh is a Marketing Research Analyst at Hevo who specializes in data analysis. He is a BITS Pilani Alumnus and has collaborated with thought leaders in the data industry to write articles on diverse data-related topics, such as data integration and infrastructure. The contributions he makes through his content are instrumental in advancing the data industry.

1 -- https://res.cloudinary.com/hevo/image/upload/v1729852677/hevo-blog/ebook-downloadable-papers/ebooks/How_a_Modern_Data_Stack_Creates_a_360_Degree_View_pecmu0.pdf --- Download Your EBook For Free - Exit Intent Popup
Related Articles
How to use DAX Studio in Power BI DAX Functions in Power BI: 8 Popular Function Types