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.
Table of Contents
Prerequisites
This is what you need for this article:
What Is Power BI?
Image Source: PEI
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.
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 Is Power BI DAX?
Image Source: kumail raza
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 in this sales table.
Image Source: Mitchell Pearson
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] )
)
Image Source: Mitchell Pearson
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] )
)
The expression runs as follows:
Image Source: Mitchell Pearson
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.
Image Source: Enterprise DNA
In the above figure, you can see that the date slicer has been set to operate between January and September. The result shows that the cumulative sales formula is working well. The reason is that it was able to show cumulative sales between January and September.
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.
Hevo lets you migrate your data from your applications to any Data Warehouse of your choice like Amazon Redshift, Snowflake, Google BigQuery, or Firebolt, within minutes to be analyzed in Power BI. What’s worth noting is that Hevo is a No-Code Data Pipeline that can be set up by any individual without the requirement of any extensive training or help. In case of need, our outstanding customer service team will help you configure your pipelines to your own needs.
Visit our Website to Explore Hevo
Hevo Data with its strong integration with 100+ Sources & BI tools allows you to 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 using BI tools such as Power BI.
Give Hevo Data a try and Sign Up for a 14-day free trial today. Hevo offers its users different pricing plans for different use cases and 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.
Skilled in freelance writing within the data industry, Nicholas is passionate about unraveling the complexities of data integration and data analysis through informative content for those delving deeper into these subjects. He has written more than 150+ blogs on databases, processes, and tutorials that help data practitioners solve their day-to-day problems.