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

Data filtering reduces the noise, so you can focus on more important things.

Table of Contents

Filtering data is one of the most common ways to analyze data. Whether you are a financial expert, a data scientist, or a marketing or sales person, filtering of data cuts through the clutter and presents what is useful. In most cases, when data is filtered, the result is a subset of data that presents an opportunity to prepare for future analysis.

Unarguably, the renowned and most popular Business Intelligence tool of our time, Power BI, also offers data filtering, through the use of DAX filter functions. 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. Make sure you then read thoroughly to grasp the concepts fully.

Table of Contents

• What is Power BI?
• Business Benefits of Using Power BI
• What are DAX Functions?
• What are Power BI DAX Filter Functions?
  • DAX Filter Function Syntax
  • What are DAX Filter Functions Used For?
• List of DAX Filter Functions
• DAX Filter Functions in Action
  • DAX Filter Function as a Table Expression
  • DAX Filter Function With Multiple Conditions
  • Using Filter Function in a Measure
  • Filter Function Doesn’t Change Your Columns
• Nesting DAX Filter Functions
• Conclusion

What is Power BI?

The Gartner Magic Quadrant Report has rewarded Microsoft Power BI as the leader in the Business Intelligence industry for 14 consecutive years. Clearly, that explains a lot about Power BI.

Power BI is a Microsoft Business Intelligence suite to analyze data and share insights. It features capabilities such as

• Dataset filtration,  
• Visual-based data discovery, 
• Interactive dashboards, 
• Augmented Analytics,
• Natural Language Q & A Question Box,
• Office 365 App Launcher, and many more.

Microsoft Power BI runs on desktop and mobile, on the cloud, which means your teams can collate, manage, and analyze data from anywhere. Power BI allows you to upload data from multiple sources like Excel, CSV, SQL Server, MySQL database, PDF, Access, XML, JSON, and a plethora more. 

Microsoft Power BI collects, analyzes, and transforms your data into actionable insights. These insights are frequently provided using aesthetically appealing and simple-to-understand charts and graphs, which enables faster decision-making in your organization. When combined with Azure Cloud, Power BI can accelerate big data preparation and analysis and reduce your time to decision planning tremendously.

For more information on Power BI, do check out Understanding Microsoft Power BI: A Comprehensive Guide. If you would like to learn about the different types of visuals to include in your next Power BI report, you can refer to this guide – Ultimate Guide on Power BI Visuals: 20+ Types to Use in 2022. 

Business Benefits of Using Power BI

• Interactive & Easy-to-Use Interface: Nothing can be more beneficial than a simple-to-use interface with a drag and drop functionality that lets you create data visualizations using a few clicks. Microsoft Power BI enables everyone at every level of your organization to make confident decisions using up-to-the-minute analytics.
• Multiple Dataset Sources: Using Power BI, you can import data from a plethora of data sources, with support for both structured and unstructured data.
• Industry-leading AI: Microsoft’s strong base in artificial intelligence enables Power BI users to prepare data, build machine learning models, and find insights quickly from both structured and unstructured data.
• Exceptional Excel Integration: With Power BI, your users can easily collect, analyze, publish, and share Excel business data. Excel queries, data models, and reports can be readily connected to Power BI Dashboards by anybody who is acquainted with Office 365.
• Real-time Stream Analytics: Power BI fetches real-time data insights into your data visualizations to keep your teams up-to-date and ready to make the right decisions. 
• Turn Insights to Action: Using Microsoft Power Platform, your teams can deliver actions quickly by combining Power BI with Power Apps and Power Automate. Microsoft’s strong integration allows your users to easily build business applications and automate workflows.

What are DAX Functions?

Data Analysis Expressions, commonly pronounced as DAX, is a rich function library developed to interact with data in Microsoft’s own platforms like Microsoft PowerPivot, Power BI Desktop, and SQL Server Analysis Services (SSAS) Tabular models. It’s similar to Microsoft Excel functions but comes with additional power and flexibility to work with relational data and perform dynamic aggregation.

Microsoft DAX query language is designed to simplify data analysis, crunch numbers, and visualize patterns using easy-to-follow and use formulas. The DAX function library comprises more than 250 functions in different categories like

• Date and Time Functions
• Time Intelligence Functions
• Information Functions
• Logical Functions
• Mathematical and Trigonometric Functions
• Statistical Functions 
• Text Functions 
• Parent-Child Functions, and many more.

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

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.

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.

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"
)

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

Recommended Articles

• All About Power BI LIVE Connection Simplified 101
• How to Use a Power BI Date Slicer or Filter: 2 Easy Steps
• Power BI Conditional Formatting: The Ultimate How-To Guide
• How to Use SUMX Power BI Function and How is it Different from SUM?

Conclusion

The filter function 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 which qualify true are aggregated as a separate table and given to the user as an output. In this tutorial, we looked upon 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 Data can connect your frequently used databases and SaaS applications like MySQL, PostgreSQL, Salesforce, Mailchimp, Asana, Trello, Zendesk and other 100+ data sources to a Data Warehouse with a few simple clicks. It can not only export data from sources & load data in the destinations, but also transform & enrich your data, & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis in Power BI. 

Using Hevo is simple, and you can set up a Data Pipeline in minutes without worrying about any errors or maintenance aspects. Hevo also supports advanced data transformation and workflow features to mold your data into any form before loading it to the target database.

Visit our Website to Explore Hevo

Hevo lets you set up your data pipelines from your favorite applications to any Data Warehouse of your choice like Amazon Redshift, Snowflake, Google BigQuery, or Firebolt, within minutes to be subsequently analyzed in BI solutions like Power BI.

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 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.