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

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.

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.

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:

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.

Let’s create another measure.

DAX

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.

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

The output of the above dax function is shown below:

Behavior:

• ALL() removes all existing filters on the entire table (Sheet1) before applying any filter conditions.
• Then, Sheet1[Country] = "Australia" is applied.
• Because ALL() removes all filters, this measure ignores any other filter context applied in the report (such as from slicer, visual interaction or any other filter.).
• The only filter that remains is Sheet1[Country] = "Australia", ensuring that the measure always returns the total 10th Marks for Australia without being affected by slicers or visual filters.

Similar functions in Power BI DAX are:

• ALLSELECTED
• ALLEXCEPT

Difference Between ALL, ALLSELECTED, and ALLEXCEPT Function