DAX

Dax Function: ALL

Q: What does the ALL function do in Power BI?

The ALL function removes filters from specified tables or columns, allowing calculations on the full dataset.

Q: When should I use ALL in a measure?

You should use it when performing calculations like grand totals or percentages that require the full dataset.

Q: Can ALL be used with CALCULATE?

Yes, the ALL function is often paired with CALCULATE to manage filter contexts in complex calculations.

Q: What is the difference between ALL and REMOVEFILTERS?

Both functions remove filters, but REMOVEFILTERS is more explicit and improves code readability.

Q: Does ALL work with multiple columns?

Yes, you can use ALL with multiple columns or nested within other DAX functions.

Category: Filter Functions

ALL is a Data Analysis Expressions (DAX) function in Power BI that removes filters from specified columns or tables, effectively returning all rows or unique values from the data.

Purpose

The ALL function is primarily used to override filters in calculations, enabling you to perform comparisons or calculations on the full set of data, regardless of active filters.

Type of Calculations

The ALL function is commonly used in calculations that require unfiltered data, such as computing totals, averages, or percentages of a whole, regardless of filter context.

Practical Use Cases

Calculating Grand Totals: Compute totals independent of filters.
Percentage of Total: Use in measures to calculate percentages.
Dynamic Aggregations: Provide unfiltered reference values for comparison.


ALL(<table_or_column>)</table_or_column>

Parameter	Type	Description
`table_or_column`	Table/Column	The table or column for which filters are removed.

How Does ALL Dax Works

The ALL function works by ignoring filters applied to the specified column or table. Internally, it removes the filter context for the given data and evaluates the expression in a “global” or “all-inclusive” scope.

Example Formula:

% of Total Sales =
DIVIDE(
SUM(Sales[Amount]),
CALCULATE(SUM(Sales[Amount]), ALL(Sales))
)

Here, ALL(Sales) removes filters to calculate total sales, ensuring the percentage calculation uses the global total.

What Does It Return?

The function returns:

Table: If a table is passed, it returns the entire unfiltered table.
Column: If a column is passed, it returns all unique values of that column, unfiltered.

When Should We Use It?

When you need to ignore filters: For example, in measures that compute total values.
When creating percentage metrics: Such as “percentage of total.”
When comparing filtered data with unfiltered data.

Examples

Basic Usage :


ALL(Sales)

Removes all filters from the Sales table.

Column Usage


ALL(Sales[Region])

Returns all unique regions in the Sales[Region] column, ignoring filters.

Advanced Usage


Filtered Total =
CALCULATE(
SUM(Sales[Amount]),
ALL(Sales[Region])
)

Calculates the total sales across all regions, ignoring region-specific filters.

Tips and Tricks

Use with CALCULATE: The function is often paired with CALCULATE for dynamic filtering scenarios.
Combine with ALLEXCEPT: When partial filter removal is needed, ALLEXCEPT can complement ALL.
Beware of Filter Removal: Using ALL unintentionally can lead to unexpected results in your reports.

Performance Impact of ALL DAX Function:

Using ALL on large datasets can impact performance, especially when combined with other intensive calculations. Optimize by limiting the function’s scope.

Related Functions You Might Need

ALLEXCEPT: Removes filters except for specified columns.
ALLSELECTED: Considers filters applied in a visual context.
REMOVEFILTERS: Explicitly removes filters for cleaner code.

Want to Learn More?
For more information, check out the official Microsoft documentation for ALL You can also experiment with this function in your Power BI reports to explore its capabilities.

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.

1. What does the ALL function do in Power BI?

It removes filters from specified tables or columns for unfiltered calculations.

2. When should I use ALL in a measure?

Use it when you need calculations based on the full dataset, such as percentages or grand totals.

3. Can ALL be used with CALCULATE?

Yes, it’s commonly used with CALCULATE to control filter contexts dynamically.

4. What is the difference between ALL and REMOVEFILTERS?

While both remove filters, REMOVEFILTERS is more explicit and recommended for better readability.

5. Does ALL work with multiple columns?

Yes, you can reference multiple columns within a table or use nested functions.

Back to module

Dax Function: Filter Functions

Dax Function: ALLCROSSFILTERED