Power BI ALL

What is the Power BI All function?

The Power BI All is a filter function that allows you to remove all the filters in reference to a base table or a base column, regardless of whether any existing filters have been applied or not. When you apply this function, it returns all the records in a specified table or the values in a specified column.

This function is an excellent choice for scenarios where you clear all the filter conditions and apply calculation logic on all the records of the table.

What is the Power BI All function?

The Power BI All function removes all the filters in reference to a base table or a base column regardless of whether any existing filters have been applied or not.
It returns all the records in a specified table or the values in a specified column.
You can specify the All function in multiple syntax choices that include: ALL(), ALL(Table), ALL (Column[, Column[, …]]), ALLEXCEPT(Table, Column1 [, Column2]…)
The Power BI All function can be used in a measure, column, and with a table parameter as well.
Consider the limitations before you use ALL function for your reporting requirements.

Syntax

The syntax for the Power BI All function is outlined below:

ALL( [<table> | <column>[, <column>[, <column>[,…]]]] )

It accepts the below parameters that include:

Parameter Name	Mandatory/Optional	Description
Table	Optional	The table on top of which you want to clear any applied filters
Column	Optional	The column on top of which you want to clear any applied filters

The table below highlights how you can use the Power BI all with filter or without any filter conditions.

Function and syntax	Usage Description
ALL()	If you don’t specify any parameter, all the filters are removed in the context of the evaluation. Note: You can only use this syntax to remove all filters. This expression doesn’t return any table.
ALL(Table)	If you specify a table to the function, all the filters on the specified table will be removed. It means all the records from the specified table will be returned. Use this option if you are performing data aggregations or grouping on your dataset.
ALL (Column[, Column[, …]])	You can use this syntax to remove all the filters from the input columns in the table. Ensure that all the columns are from a single table.
ALLEXCEPT(Table, Column1 [,Column2]…)	This option allows you to remove all the context filters in the table other than the ones applied to the specified columns.

How to use the Power BI All function?

To use Power BI All, follow the steps highlighted below:

Step 1: Launch Power BI Desktop in your system.

Step 2: Import the dataset into Power BI using the Get data option and load it into Power BI using the Load button.

Step 3: Navigate to the Modeling tab and click on the New measure option.

Step 4: In the formula bar, specify the logic for the measure by writing the DAX expression. Save the changes by clicking on the Enter or Commit icon.

Step 5: Navigate to the Visualizations pane and choose a visual icon. Map the newly created column to the visual to create a report in the report canvas.

Note: If you are facing issues with Power BI All not working, then consider the below options:

Troubleshoot the error and look for the latest Power BI documentation for more details.
Search for similar errors in the Microsoft Power BI Community.
Validate the dataset to ensure the dataset is accurate.

Examples

In this section, we will go through a few examples demonstrating the use of the All function in Power BI with different variations of input parameters.

Example #1 – Using ALL() inside a Measure

In this example, we will demonstrate using the Power BI ALL function inside a measure. To demo this example, we will be using the Top_50_Fast_Food_US dataset. The Top_50_Fast_Food_US dataset contains information on the total sales, sales per unit, franchise units, company-owned units, and unit change from 2018 for the top 50 fast-food chains in the U.S. in 2020.

To use the Power BI All function inside a measure, follow the instructions below:

Step 1: Import the dataset into Power BI using the Get data option and click on the Load button to load it into Power BI.

Step 2: Right-click on the table and select the New measure option.

Step 3: In the formula bar, specify the logic for the measure using the DAX expression.

Here, we are creating a calculated measure % of Total Units sold to calculate the overall distribution of the number of product units sold. The All function used here will remove any filter applied to the category column.

% of Total Units sold = DIVIDE(CALCULATE(SUM(top_50_fast_food_US[total_units_2019])),

CALCULATE(SUM(top_50_fast_food_US[total_units_2019]),ALL(top_50_fast_food_US[category])))

Click on the Commit icon to save the changes. It will create a measure of your dataset.

Step 4: Repeat the above step to create another calculated measure for your dataset. This measure will store the overall distribution of product sales.

% of Total Sales = DIVIDE(CALCULATE(SUM(top_50_fast_food_US[sales_in_millions_2019])),

CALCULATE(SUM(top_50_fast_food_US[sales_in_millions_2019]),ALL(top_50_fast_food_US[category])))

Step 5: Navigate to the Visualizations pane and choose the Table visual icon. Drag and drop the newly created measures to the visual section. You will see the Table visual in the report canvas.

You can see the overall contribution of each category towards sales and units sold.

Example #2 – Using ALL() with Multiple Column Parameters

In this example, we will demo the use of the All function with multiple column parameters. We have used the Global Sales Records dataset for the demo purpose. The Global Sales Records dataset contains global sales details, including the country and region view.

Follow the instructions outlined below to use All with multiple column parameters:

Step 1: Open Power BI Desktop, navigate to Home – Get data, and click on the Load button to load the data into Power BI.

Step 2: Navigate to the Modeling tab. Choose the New measure option.

Step 3: Create a measure by writing a DAX expression in the formula bar using the All function.

Here, we are creating a calculated measure to compute the overall contribution to global sales. The All function used here will remove the filters from the Item Type and Sales Channel fields, while any filters on the other columns will remain intact.

% contribution to total global sales = DIVIDE(

CALCULATE(SUM(Sales_records_global[Total Revenue])),

CALCULATE(SUM(Sales_records_global[Total Revenue]), ALL(Sales_records_global[Item Type],Sales_records_global[Sales Channel]))

)

Click on the Commit icon to save the changes.

Step 4: Create a data visualization by navigating to the Visualizations pane and choosing the Table visual icon. Drag and drop the newly created measures to the visual section.

Step 5: Add slicers to the visualization by choosing Slicers from the Visualizations pane. Map the data fields from the Data pane.

Once you have created the slicer, your final visual looks as shown below:

Step 6: Apply filter to the Item Type as Beverages. You will notice that while other aggregated columns have changed in the context of the slicer, the Total Sales and % contribution to global sales haven’t changed due to the use of the All function.

Example #3 – Using ALL() with a Table Parameter

This example primarily focuses on demonstrating the use of the All function with a table parameter using the New York Property Sales dataset. The New York Property Sales dataset contains the sales details of various real estate prices around New York City in the US.

You can follow the steps highlighted below to use the Power BI All function with a table parameter:

Step 1: Choose the dataset using the Get data option. Load the dataset into the Power BI data model by clicking on the Load button.

Step 2: Once the dataset is loaded, right-click on the table and select the New measure option.

Step 3: Write the DAX expression in the formula bar, and specify using the All function to create a calculated measure.

We have created a measure to calculate the percentage distribution of total units sold using the All function, as shown below.

% of units sold = SUMX(Final_Property_Sales,Final_Property_Sales[TOTAL_UNITS])/SUMX(ALL(Final_Property_Sales),Final_Property_Sales[TOTAL_UNITS])

To save the changes, click on the Commit icon. I0074 will create a new measure in the dataset.

Step 4: Navigate to the Visualizations pane and choose the Table visual icon.

From the Data pane, drag and drop the data fields, including the newly created measure, and map them to the report canvas. It will create a Table visual, as shown below:

Important Things to Note

The Power BI All function is not used as a standalone function. Instead, it’s used as an intermediate function to change the resulting output over which you can perform calculations.
There may be a few specific scenarios where the ALL() function used in a DAX expression may not clear all the filters. They include scenarios where auto-exist is available. Suppose you are filtering on multiple columns on a single table and have created a measure on the table that uses the ALL() function. In that case, the presence of auto-exist will merge all the filters into a single filter. Then, the filter is applied to the existing combination of values, leading to unexpected results.
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Frequently Asked Questions (FAQs)

1. Can I use the ALL function in Power BI with multiple columns?

Yes, you can use the ALL function in Power BI with multiple columns. The syntax for using this variation is provided below:

ALL (Column[, Column[, …]])

This usage removes all filters from the specified columns in the table. However, note that all other filters on other columns (not specified in the input parameters) in the table still apply. While specifying the columns, ensure that all the input columns to the All function should be from the same table. Use this option to remove the filters selectively on a few specific columns while keeping other context filters in place.

2. Does the ALL function affect visuals in Power BI?

The ALL function does not directly affect the visuals in Power BI. The ALL function removes any filters from a table or a column and returns the entire set of values. Hence, it can change the result outputs of the calculations involving the All function that are used in the visuals in the Power BI.

3. What is the difference between all and allexcept in Power BI?

The key differences between the ALL and ALLEXCEPT functions in Power BI are outlined below:

• ALL
◦ The ALL function removes all filters from the specified table or columns.
◦ It returns all the rows from the table or all the values from the columns, ignoring any filters applied by slicers, cross-filters, or other filters applied to the report.

• ALLEXCEPT
◦ The ALLEXCEPT function removes filters from all columns in a table except for the specified columns.
◦ It keeps the filters on the specified columns while removing filters from all other columns.

4. What is the difference between all and allselected in Power BI?

The key differences between the ALL and ALLSELECTED functions in Power BI are highlighted below:

• ALL
◦ The ALL function removes all filters from the specified table or columns.
◦ It returns all the rows from the table or all the values from the columns, ignoring any filters applied by slicers, cross-filters, or other filters applied to the report.

• ALLSELECTED
◦ The ALLSELECTED function retains filters that have been applied by slicers, report interactions, or other selections made in the report.
◦ It removes only the filters from tables or columns that are not included in the existing report context but retains filters on columns that are in the current context.

Power BI ALL