ALL
Returns every row or every value of a table or column, ignoring the filters on it.
ALL(<table> | <column> [, <column> [, ...]])Returns: A table. Inside CALCULATE, it removes the filters on the tables or columns you name.
What it does
ALL removes filters. Used as a table function, it returns every row or every distinct value of a column, whatever the slicers say. Used as a filter inside CALCULATE, it clears the filters on the tables or columns you name. A typical question: "What share of company-wide sales does this store represent?"
Syntax and arguments
- table or column: a whole table, which clears every filter on that table, or one or more columns, which clears only those columns.
- Filters on other tables still apply. Clearing
Storesdoes not clear a slicer on the date table.
Example
A store report shows each store's share of company sales. The denominator must ignore the store selection.
Share of Company Sales =
DIVIDE (
[Total Sales],
CALCULATE ( [Total Sales], ALL ( Stores ) )
)
Each store row is divided by the total for all stores within the other filters, such as the selected dates. The shares add up to 100 percent across the store rows when no store filter is applied.
Example: a second use
A card shows how many stores are in the model, regardless of what the user has selected.
All Stores Count =
COUNTROWS ( ALL ( Stores[StoreName] ) )
Placed next to a count of visible stores, this gives a "stores selected out of total" view. Because ALL ignores slicers on the store name, the figure stays the same however the user filters.
Things to watch
- ALL ignores slicers on the cleared table, so a percent of ALL does not change when the user slices. If the reader expects the visible total, use
ALLSELECTED. ALL(Table)clears every column of that table, not just the one you had in mind.ALL(Table[Column])is narrower.- Used as a table function over a fact table, ALL can be very large. Use a dimension column where possible.
- ALL can include the blank row that a broken relationship adds.
ALLNOBLANKROWleaves it out. REMOVEFILTERSdoes the same job inside CALCULATE and reads more clearly, but it cannot return a table.
In business terms
Percent of total, grand total denominators, company-wide benchmarks and "all customers" counts. Month-end and board reports often compare a segment with total company revenue, and ALL is the function that provides the total.