Data Analysis is a great way of extracting insights from data. These insights help businesses and individuals to make evidence-based decisions. A business that makes evidence-based decisions has an advantage over its competitors. To analyze data, you will need a Data Analysis Tool. This tool will help you visualize your data and run different functions against your data for information extraction.
One such example is Power BI, a Business Intelligence Solution offered by Microsoft. Power BI is very popular among businesses due to its ease of use and the benefits it brings to an organization.
With Power BI, you can present your data visually for ease of understanding. Power BI also supports different functions and measures that you can use to extract insights from your data. For example, DAX functions like DAX ALLSELECTED can be used by Power BI users to create new expressions and interact with data. Every Power BI user should familiarize themselves with DAX functions. In this article, we will be discussing the DAX ALLSELECTED function in detail.
A fully-managed No-code Data Pipeline platform like Hevo helps you integrate and load data from 150+ different sources to a destination of your choice in real-time in an effortless manner.
Why Choose Hevo?
- Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
- Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
- Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
Hevo can help you scale your data infrastructure as required.
Get Started with Hevo for Free
Prerequisites
This is what you need for this article:
What Is Power BI?
Power BI is a Business Intelligence (BI) Tool and a Data Visualization Platform offered by Microsoft that allows organizations to analyze business data and generate reports. Power BI comes with a set of built-in tools, apps, and connectors that can deeply delve and work with data to provide actionable insights, immersive visuals, and interactive reports.
Power BI is a self-service Business Intelligence Service, which means that you can easily aggregate, analyze and visualize data to produce engaging visual reports within minutes of uploading your data. Power BI lets you pull data in from multiple sources such as Oracle, SAP, or a Data Warehouse of your choice. It can handle everything from your simple Excel file all the way to massive amounts of data in databases. You can leverage Power BI Charts, Graphs, KPIs, Reports, and Dashboards to analyze the data and generate interactive insights for your business.
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.
Key Features of Power BI
Power BI has proven to be a reliable and user-friendly Data Analysis and Visualization Tool. Let’s discover some of the key features responsible for its immense popularity.
- Easy Integrations: Power BI offers integrations with multiple connectors that allow users to pull in data from various data sources.
- AI Support: Power BI allows users to deploy Artificial Intelligence (AI) techniques such as Image Recognition and Text Analytics to prepare data, develop Machine Learning models, and quickly extract actionable insights from structured and unstructured data.
- Report Sharing: Power BI is built for developing security that allows teams to share access in a very controlled manner. Users can easily share their reports with other team members without compromising data security.
- Real-Time Dashboards: Power BI has the capability to display real-time data and visuals in any report or dashboard. Power BI dashboards update in real-time, allowing users to instantly solve issues and uncover opportunities.
- Customized Visualization: Power BI offers high customizability and allows users to leverage its custom visualization library to create visualizations as per their needs. In addition to that, users can also generate highly customizable visuals for their next Power BI report by using open-source data-viz modules from R and Python.
What Is Power BI DAX?
The Power BI Data Analysis Expressions, commonly pronounced as DAX, comprises over 200 functions, operators, and constants that provide immense flexibility in Data Analysis tasks. It is developed to interact with data in Microsoft’s own platforms like Microsoft PowerPivot, Power BI Desktop, and SQL Server Analysis Services (SSAS) Tabular models.
One such function discussed in this guide is DAX ALLSELECTED which removes context filters from columns & rows. More information on DAX ALLSELECTED function will be covered in the next section.
What Is DAX ALLSELECTED Function?
The DAX ALLSELECTED function in Power BI returns all the values in a column, or all the rows in a table while ignoring all the filters that might have been applied inside the query, and keeping the filters that might have come from the outside.
This means that the function ignores the filters that come from the visual, but respects filters that come from slicers and filters outside the visual.
This makes it different from the DAX ALL function. Whereas the DAX ALL function ignores all filters regardless of where they come from, the DAX ALLSELECTED function ignores filters that come from the inner query. Although the difference may seem to be subtle, it can bring a huge difference in the values that you get within a particular context. The DAX ALLSELECTED function can help you find visual totals in queries.
Let’s now have a look at how you can use the DAX ALLSELECTED function to query your data:
DAX ALLSELECTED Function Syntax
The DAX ALLSELECTED function is used with the following syntax:
ALLSELECTED([<tableName> | <columnName>[, <columnName>[, <columnName>[,…]]]] )
The parameters described above are:
- tableName– The name of the table using the standard DAX syntax. It’s an optional parameter, and it cannot be an expression.
- columnName– The name of a column using the standard DAX syntax. It’s an optional parameter, fully qualified, and it cannot be an expression.
The function returns the context of the query without column and row filters. It takes a column name or table name as the argument. If there is more than one argument, they should be the names of columns within the same table.
Note: The DAX ALLSELECTED function cannot be used in DirectQuery mode when used in calculated fields or row-level security fields.
Next, we will be giving practical examples on how to use this function.
DAX ALLSELECTED Function Examples
In this section, we will be giving examples of how you can use the DAX ALLSELECTED function in various scenarios. Here we will use a dataset on sales. Consider the following calculations done to a sales table.
DAX ALLSELECTED Example 1
In some cases, you may want to return the total sales for all countries in all scenarios. Consider the following expression that uses the ALL function:
Total Sales All Countries =
CALCULATE (
[Total Sales],
ALL('Sales Territory'[Sales Territory Country] )
)
The above figure shows that the countries Australia, Canada, and France have been selected for the slicer. Despite this, the measure returned the total sales across all countries. That is how the DAX ALL function works.
Let’s now write the above expression using the DAX ALLSELECTED function and see how it works.
Total Sales AllSelected Countries =
CALCULATE (
[Total Sales],
ALLSELECTED(‘Sales Territory'[Sales Territory Country] )
)
From the above figure, you can tell that the DAX ALLSELECTED function has provided us with a different way of analyzing the data. The goal was to return the total sales for all countries that have been selected in the slicer. This could have been tricky without the DAX ALLSELECTED function.
DAX ALLSELECTED Example 2
Let’s say you’ve already found the highest revenue-producing month on your sales calendar. Now you would like to remove filters specifically on the dates within the current context. Consider the expression given below that gets applied to your sales table to do the same:
Highest Sale Month ALLSELECTED =
CALCULATE (
MAXX( VALUES( Dates[Month & Year] ), [Total Revenue] ),
ALLSELECTED ( Dates )
)
In the above expression, we are using the DAX ALLSELECTED function to remove filters on the dates in the current context, as selected on the report page.
DAX ALLSELECTED Example 3
Let’s demonstrate how to use the DAX ALLSELECTED function to calculate cumulative sales. The goal is to show cumulative sales by month name rather than by month and year. The following expression demonstrates this:
Cumulative Sales =
CALCULATE( [Total Sales],
FILTER( ALLSELECTED( Dates ),
Dates[Dates] <= MAX( Dates[Date] ) ) )
The above formula calculates sales within the selected date range. The DAX ALLSELECTED function in this case shows the values depending on whatever date range that is selected within the report.
That is how to work with the DAX ALLSELECTED function in Power BI.
Conclusion
Power BI is a Data Analysis and Visualization Tool developed by Microsoft. It helps individuals and businesses to extract insights from their data. In this blog, we briefed you about DAX functions – Operators and Functions that help Power BI users to create new formulas and expressions, with emphasis on DAX ALLSELECTED function.
The DAX ALLSELECTED function returns all the values in a column, or all the rows in a table and ignores all the filters that have been applied inside the query, and keeps the filters that have come from the outside. It is different from the ALL function in that the ALL function ignores all filters regardless of where they come from, while the ALLSELECTED function ignores filters that come from the inner query.
Power BI is a wonderful tool for extracting insights from your data, as long as you know how to supply Power BI with your data. In a world of hundreds of SaaS Applications and Databases, merging and transferring all your data to a single repository is not an easy job.
You can try Hevo’s 14-day free trial. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs!
Share your experience of understanding the DAX ALLSELECTED Power BI function in the comments section below. We’d be happy to hear your opinions.
FAQs
1. What does ALLSELECTED do in DAX?
ALLSELECTED removes filters applied at the visual level but preserves any filters from slicers or outside visuals. It’s often used for calculations like percentages or comparisons within the filtered context.
2. What is the difference between ALL, ALLSELECTED, and ALLEXCEPT?
ALL: Removes all filters from a column or table.
ALLSELECTED: Keeps slicer/external filters but removes visual-level filters.
ALLEXCEPT: Removes filters except for specified columns, retaining those columns’ filter context.
3. What is the difference between REMOVEFILTERS and ALLSELECTED in DAX?
REMOVEFILTERS: Removes filters from a table or column, like ALL, and is more readable in newer DAX.
ALLSELECTED: Preserves slicer and external filters but clears visual-level filters, making it more context-aware.
Nicholas Samuel is a technical writing specialist with a passion for data, having more than 14+ years of experience in the field. With his skills in data analysis, data visualization, and business intelligence, he has delivered over 200 blogs. In his early years as a systems software developer at Airtel Kenya, he developed applications, using Java, Android platform, and web applications with PHP. He also performed Oracle database backups, recovery operations, and performance tuning. Nicholas was also involved in projects that demanded in-depth knowledge of Unix system administration, specifically with HP-UX servers. Through his writing, he intends to share the hands-on experience he gained to make the lives of data practitioners better.
