Data filtering reduces the noise, so you can focus on more important things.
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?
Image Source: Towards Data Science
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.
A fully managed No-code Data Pipeline platform like Hevo Data helps you integrate and load data from 100+ Data Sources (including 40+ Free Sources) such as Power BI to a Data Warehouse/Destination of your choice in real-time in an effortless manner.
Hevo, with its minimal learning curve, can be set up in just a few minutes allowing users to load data without having to compromise performance. In addition to the 100+ data sources, Hevo also allows integrating data from non-native sources using Hevo’s in-built Webhooks Connector.
Its strong integration with umpteenth sources allows users to bring in data of different kinds in a smooth fashion without having to code a single line.
Get Started with Hevo for Free
Check out some of the cool features of Hevo:
- Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
- Connectors: Hevo supports 100+ Integrations to SaaS platforms, Files, Databases, BI tools such as Power BI, and Native REST API & Webhooks Connectors. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake Data Warehouses; Amazon S3 Data Lakes; MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL Databases to name a few.
- Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
- 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
- Scalable Infrastructure: Hevo has in-built integrations for 100+ sources (Including 40+ Free Sources) such as Power BI that can help you scale your data infrastructure as required.
- 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!
What are DAX Functions?
Image Source: K21 Academy
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.
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:
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. |
Power BI DAX filter functions from Microsoft
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, 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"
)
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
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.