ALL DAX Function in Power BI

The ALL function returns a table containing all the rows, or all the values in a column, ignoring any filters that might have been applied (ignores internal visual filter and ignores any external filters also like from slicers). This function is useful for clearing filters and creating calculations on all the rows in a table.

DAX Syntax 1) ALL() The ALL() is used to remove all the filters but not return the table. 2) ALL (Table) The ALL(Table) is used to remove all the filter context from the given table. 3) ALL (Column1[, Column2, Column3….]) Here, ALL with column names in its argument, removes all filter context from the given columns of the table and keep filter context from other columns. Please note all the columns specified coming from the same table.

Note: The argument to the ALL function must be either a reference to a base table or a reference to a base column. We cannot use table expressions or column expressions with the ALL function.

Example: Let’s create a measure, in which all the filters applied on the table named “sheet1” is ignored.

DAX

All Sum Measure = CALCULATE(SUM(Sheet1[10th Marks]), ALL(Sheet1))

The formula calculates the sum of the "10th Marks" column from the "Sheet1" table, disregarding any filters applied to the "Sheet1" table within the context of the calculation (even outer filters coming from slicers, for example). This effectively gives us the total sum of all the values in the “10th marks” column of the "Sheet1" table.

ALL dax function in Power BI

Let’s select some Names from the Name slicer, correspondingly the Country slicer is filtered but note that the “All Sum Measure” value still not change as ALL function, ignores any filter coming from the Sheet1 table in the calculation.

ALL dax function in Power BI

Let’s create a table visual and add the Country, 10th Marks and All Sum Measure in the table visual, as shown in the image below:

ALL dax function in Power BI

If we select specific countries from the slicer even though the measure calculation is not affected by the slicer selection as shown in the image below.

ALL dax function in Power BI

Let’s create another measure.

DAX

All Sum Measure1 = CALCULATE(SUM(Sheet1[10th Marks]), ALL(Sheet1[Country]))

Also add a matrix visual to better understand the filter context. Here, we can see that the “All Sum Measure1” respects the filter context coming from the Name column.

ALL dax function in Power BI

Combine ALL function with other filters in CALCULATE function in Power BI

Let’s create another measure named “All Sum Measure with ALL() Filter”.

DAX

All Sum Measure with ALL() Filter = CALCULATE ( SUM ( Sheet1[10th Marks] ), Sheet1[Country] = "Australia", ALL() )

The output of the above dax function is shown below:

ALL dax function in Power BI

Behavior:

Similar functions in Power BI DAX are:

Difference Between ALL, ALLSELECTED, and ALLEXCEPT Function

FunctionVisual FiltersSlicer FiltersPage/Report FiltersEffect
ALL(Sales)IgnoredIgnoredIgnoredRemoves all filters context from the table.
ALLSELECTED(Sales)IgnoredRespectedRespectedRemoves visual filters from the table.
ALLSELECTED(Sales[Product])Ignored (for Product)RespectedRespectedRemoves visual filters only for Product.
ALLEXCEPT(Sales, Sales[Product])Respected only for Product columnRespected only for Product columnRespected only for Product columnRemoves all filters context from the table except for the specified column.