Row Context in DAX - PowerBI - ALL, ALLSELECTED & ALLEXCEPT

This post is about providing differences and an overview of the most commonly used expression in PowerBI DAX which are ALL , ALLSELECTED & ALLEXCEPT.

ALL

Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table. 

This function removes all filters from the specified table. In effect, ALL(Table) returns all of the values in the table, removing any filters from the context that otherwise might have been applied. This function is useful when you are working with many levels of grouping, and want to create a calculation that creates a ratio of an aggregated value to the total value. 

Example:

 = CALCULATE(SUM(FactSales[Sales]), ALL('Sales Territory'[Sales Territory Country])) 

In this example, The goal is to return Total Sales for the country ignoring context filters on the table. 

ALLSELECTED

Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.

The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters. This function can be used to obtain visual totals in queries.

Example:

 = CALCULATE(SUM(FactSales[Sales]), ALLSELECTED('Sales Territory'[Sales Territory Country])) 

In this example, The goal is to return Total Sales for the country selected in the slicer. 

ALLEXCEPT

 Removes all context filters in the table except filters that have been applied to the specified columns. 

This function removes all context filters in the table except filters that are applied to the specified columns. This is a convenient shortcut for situations in which you want to remove the filters on many, but not all, columns in a table. 

Example:

 = CALCULATE(SUM(FactSales[Sales]), ALLEXCEPT(DateTime, DateTime[CalendarYear]))  

Because the formula uses ALLEXCEPT, whenever any column but CalendarYear from the table DateTime is used to slice a visualization, the formula will remove any slicer filters, providing a value equal to the sum of Sales.

Thanks for reading numlytics blog!!