ALL Functions - how to use it with examples

 

ALL function

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

The ALL function is used to remove all filters from a column or table, which can be useful in some cases when you want to calculate a measure without any filters. Here are a few examples of how to use the ALL function:

Example 1: Removing filters from a column

Let's say you have a Sales table with a column named "Product Category" that you want to use in a calculation. If you want to remove all filters from this column and calculate the total sales for all categories, you can use the following DAX formula:

Total Sales = SUM(Sales[Sales Amount]) Total Sales All Categories = CALCULATE([Total Sales], ALL(Sales[Product Category]))

In this example, the ALL function removes any filters from the "Product Category" column, so the [Total Sales] calculation includes sales for all categories.

Example 2: Removing filters from a table

Let's say you have a data model with multiple tables, and you want to calculate a measure that includes data from all tables, regardless of any filters that may be applied. In this case, you can use the ALL function with the UNION function to create a table that includes all rows from all tables:

Total Sales All Tables = CALCULATE(SUM(Sales[Sales Amount]) + SUM(Expenses[Expense Amount]), ALL(UNION(Sales, Expenses)))

In this example, the ALL function removes any filters from the UNION of the Sales and Expenses tables, so the [Total Sales All Tables] calculation includes sales and expenses data from all tables, regardless of any filters.