DAX ALLSELECTED to Unlock Powerful Chart Visuals

The Data Analysis Expressions (DAX) plays a major role in Excel and Power BI for data manipulation, calculations, and analysis in business intelligence. It enables efficient and flexible data modeling and reporting.

This guide will help you get a good grasp of what the DAX ALLSELECTED function is, why the DAX ALLSELECTED function is important, the use of Power BI ALLSELECTED function, and how to use ALLSELECTED in Power BI.

Table of Contents:

1. Understanding DAX ALLSELECTED Function
2. Why is the ALLSELECTED DAX Function Important?
3. What is the Use of the Power BI ALLSELECTED Function?
4. How to Use ALLSELECTED in DAX in Power BI to Evaluate Data?
5. How to Visualize Your Data Using Power BI?
6. What are the Benefits of Using the DAX ALLSELECTED Function?
7. Wrap Up

First…

Understanding DAX ALLSELECTED Function

The ALLSELECTED function in DAX returns all the values in a specified column or table. It ignores any filters that have been applied except for those that are specified explicitly using the REMOVEFILTERS or KEEPFILTERS functions.

The ALLSELECTED function comes in handy when the user wants to retrieve all the values from a column or table, disregarding the applied filters, while considering filters that are important for the analysis.

Here’s a breakdown of the ALLSELECTED function:

• ALLSELECTED([<table_or_column>])

<table_or_column>: (Optional) Specifies the column or table from the Power BI data model for which you want to retrieve all the values. If not specified, it returns all the values within the entire model context.

Why is the ALLSELECTED DAX Function Important?

The ALLSELECTED DAX function helps analysts to retrieve all the values from a specified column (or table), regardless of the applied filters, except for those explicitly specified using DAX functions like KEEPFILTERS or REMOVEFILTERS.

The function plays a major role in creating dynamic calculations that remain responsive to slicers in Power BI, enabling user interactions while accounting for critical dimensions or filters in the analysis.

Furthermore, the ALLSELECTED function plays a major role in maintaining context sensitivity within calculations. It ensures that calculations respond accurately to changes in selections and filters made by users, and also provides dynamic insights into the underlying data. This feature comes in handy in cases where the analysts have to perform comparative analyses between all values and selected values in a dataset while retaining context relevance.

What is the Use of the Power BI ALLSELECTED Function?

The Power BI ALLSELECTED function helps analysts perform calculations while putting the current filter context in reports into consideration. It returns all the values from a specified column (or table), regardless of the applied filters except for those explicitly specified using DAX functions like KEEPFILTERS or REMOVEFILTERS.

The ALLSELECTED function is vital in cases where analysts have to perform comparative analyses between all values and selected values in Power BI datasets, ensuring a comprehensive view of the data.

It gives room for the calculation of totals, averages, and other aggregations across all values. This way, the user will have a comprehensive view of data while maintaining context relevance. This feature boosts the depth and flexibility of data analysis in Power BI reports, and also empowers analysts to draw deeper insights and make more informed decisions based on a comprehensive understanding of the underlying data.

How to Use ALLSELECTED in DAX in Power BI to Evaluate Data?

Here’s a step-by-step guide to help you use the ALLSELECTED function in Power BI:

Open Power BI Desktop:

Launch the Power BI Desktop and open the dataset (or report) where you want to evaluate data using the ALLSELECTED function.

Create a New Measure or Calculated Column:

To create a new measure (or calculated column), you’ll have to right-click on the datasets in the Fields pane and select “New Measure” or “New Calculated Column,” and that depends on where you want to apply the function.

Enter the DAX Formula:

Navigate to the formula bar at the top of the screen, and using the ALLSELECTED function, enter your DAX formula. The syntax of the ALLSELECTED function is as shown below:

• MeasureName = CALCULATE(<expression>, ALLSELECTED([Table/Column]))

Replace <expression> with the aggregation or calculation you want to perform, and [Table/Column] with the column or table you want to evaluate.

Apply Additional Filters (Optional):

There are cases where you’ll have to apply additional filters (or conditions) within the CALCULATE function in Power BI. This will allow the user to further refine the evaluation of data based on specific criteria.

Press Enter:

After entering the DAX formula, press Enter to apply the formula and create the new measure (or calculated column).

Use in Visualizations:

After creating the measure (or calculated column) with the ALLSELECTED function, you can proceed to use it in the data visualizations. Drag the column (or measure) into the visualization to evaluate data based on the specified filters (or context).

Analyze Results:

The ALLSELECTED function ascertains that the evaluation of data puts the current filter context into consideration, and it also provides dynamic insights into the underlying data.

Example:

EVALUATE

CALCULATEABLE (

ADDCOLUMNS (

ALL ( ‘Product’[Category] ),

“Sales Amount,” [Sales Amount],

“Sales Sel,”

CALCULATE (

[Sales Amount],

ALLSELECTED ( Product[Category] )

)

),

Product[Category] IN { “Audio,” “Computer” }

Product [Category]	Sales Amount	Sales Sel
Audio	384,518.16	30,591,343.98
TV and Video	4,392,768.29	30,591,343.98
Computers	6,741,548.73	30,591,343.98
Cameras and Camcorders	7,192,581.95	30,591,343.98
Cell Phones	1,604,610.26	30,591,343.98
Music, Movies, and Audio Books	314,206.74	30,591,343.98
Games and Toys	360,652.81	30,591,343.98
Home Appliances	9,600,457.04	30,591,343.98

How to Visualize Your Data Using Power BI?

Stage 1: Log into Power BI, enter your email, and click “Submit.”

• Log in to Power BI.
• Enter your email address and click the “Submit” button.

• You are redirected to your Microsoft account.
• Enter your password and click “Sign in“.

• You can choose whether to stay signed in.

• Once done, the Power BI home screen will open.

Stage 2: Create a Data Set and Select the Data Set to Use in the Sankey Chart

• Go to the left-side menu and click the “Create” button.
• Select “Paste or manually enter data“.

• We’ll use the sample data below for this example.

Country	Revenue Stream	Revenue (in $)
USA	Digital Advertising Revenue	39,620,000
USA	Event Marketing Revenue	10,670,000
USA	Content Marketing Revenue	5,580,000
USA	Print & Outdoor Revenue	455,270
UK	Digital Advertising Revenue	40,710,000
UK	Event Marketing Revenue	24,770,000
UK	Content Marketing Revenue	6,330,000
UK	Print & Outdoor Revenue	552,190
DNK	Digital Advertising Revenue	47,040,000
DNK	Event Marketing Revenue	29,070,000
DNK	Content Marketing Revenue	7,740,000
DNK	Print & Outdoor Revenue	600,690
DNK	Media Relations Revenue	106,430
AUS	Digital Advertising Revenue	53,790,000
AUS	Event Marketing Revenue	38,530,000
AUS	Content Marketing Revenue	6,590,000
AUS	Print & Outdoor Revenue	9,040,000
AUS	Media Relations Revenue	6,130,000
FR	Digital Advertising Revenue	57,860,000
FR	Event Marketing Revenue	50,450,000
FR	Content Marketing Revenue	3,560,000
FR	Print & Outdoor Revenue	18,790,000
FR	Media Relations Revenue	15,460,000
IND	Digital Advertising Revenue	60,470,000
IND	Event Marketing Revenue	63,200,000
IND	Content Marketing Revenue	2,080,000
IND	Print & Outdoor Revenue	29,500,000
IND	Media Relations Revenue	30,020,000

• Paste the data table above into the “Power Query” window. Select the “Create a dataset only” option.

• Navigate to the left-side menu, and click on the “Data Hub” option. Power BI will populate the data set. If no data set has been created, you’ll get an error message. After that, you’ll have to click on “Create report.”

• Click on “Expand All” to see the chart metrics. You can check the dimensions and metrics.

• Click on “Get more visuals.” After that, search ChartExpo and select Comparison Bar Chart.

• Click on “Add.”

• You’ll see the Comparison Bar Chart in the visuals list.

• In Visual, click on License Settings and add the key. After adding the key, you’ll see the comparison bar chart.

• Here’s the final look at the Comparison Bar Chart in Power BI.

Insights

• India has the highest total revenue, and they’re closely followed by France, Australia, and Denmark.
• “Digital advertising” tends to be a significant revenue contributor in most countries, and “Event Marketing” is leading in India.
• The “Media Relations” revenue stream is absent in countries like the UK and the US.

Elevate Chart Interactivity by Leveraging DAX ALLSELECTED in Power BI:

1. Open your Power BI Desktop or Web.
2. From the Power BI Visualizations pane, expand three dots at the bottom and select “Get more visuals”.
3. Search for “Comparison Bar Chart by ChartExpo” on the AppSource.
4. Add the custom visual.
5. Select your data and configure the chart settings to create the chart.
6. Customize your chart properties to add header, axis, legends, and other required information.
7. Share the chart with your audience.

The following video will help you create a Comparison Bar Chart in Microsoft Power BI.

What are the Benefits of Using the DAX ALLSELECTED Function?

The DAX ALLSELECTED function in Power BI offers several benefits, particularly for dynamic and flexible data analysis. Here are some key advantages:

• Context Sensitivity: ALLSELECTED considers the current filter context when evaluating data, and that ascertains that calculations respond accurately to changes in slicer and filter selections. This will, in turn, maintain context sensitivity, and provide dynamic insights into the underlying data.
• Comparative Analysis: ALLSELECTED facilitates comparative analysis between all values and selected values in a dataset. It gives room for the calculation of totals, averages, and other aggregations across all values. This way, the user will have a comprehensive view of the data while maintaining context relevance.
• Dynamic Calculations: Since ALLSELECTED disregards certain applied filters while retaining others, it ensures that calculations respond accurately to slicer and filter selections. This will help maintain context sensitivity and provide dynamic insights into the underlying data.
• Comprehensive Insights: Analysts who incorporate the ALLSELECTED function into their calculations will most likely gain deeper insights into the underlying data, enhancing their ability to analyze and interpret data effectively. It ensures that calculations accurately reflect the current state of the data, empowering users to explore and analyze it in an interactive manner.
• Enhanced Reporting: Taking advantage of ALLSELECTED enhances the flexibility and depth of reporting in Power BI. It gives users the room to create dashboards and reports that accurately reflect the current filter context. This functionality will help users gain actionable insights and facilitate data-driven decision-making.

FAQs

What is the difference between all and ALLSELECTED in DAX?

The ALL function in DAX returns all values from a table or column. The ALLSELECTED function, on the other hand, returns all values while considering current filters except those explicitly removed.

What is the use case of ALLSELECTED?

The ALLSELECTED function in DAX is used in Power BI to create dynamic calculations. It provides context-aware insights and facilitates comparative analysis.

Wrap Up

The DAX ALLSELECTED function helps in retrieving all values. It also considers the current filter context in Power BI. The function plays a major role in the creation of dynamic calculations and comparative analysis. It also ensures accurate insights by accounting for applied filters.

The function enables the creation of dynamic calculations that respond to user interactions and slicer selections. This is accomplished by disregarding certain applied filters while retaining others.

By incorporating ALLSELECTED into calculations, analysts will gain deeper insights into the underlying data, and calculations will accurately reflect the current state of the data. This will, in turn, empower the user to explore and analyze data in an interactive way.

Now that you’ve figured out the DAX ALLSELECTED function, how will you incorporate it into your data visualization and analysis process?