ALL, ALLSELECTED and ALLEXCEPT

  • Post category:General
  • Post author:

Addend Analytics is a Microsoft Power BI-partner based in Mumbai, India. Apart from being authorized for Power BI implementations, Addend has successfully executed Power BI projects for 100+ clients across sectors like financial services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Australia, and India. Companies partnering with us save their valuable time and efforts of searching and managing resources while saving hugely on the development costs and hence, most small and medium enterprises in North America prefer Addend to be their Power BI implementation partner. Get a free consultation now by emailing us at kamal.sharma@addendanalytics.com.

DAX functions are thriving nowadays, they have become one of most popular and learned languages in the Business Intelligence Domain. It contains expressions which are a kind of magical in themselves and help in analysis for finding useful insights. This blog is all about providing an overview of most commonly used expressions in Power BI which are ALL, ALL SELECTED and ALL EXCEPT.

ALL – Returns all the rows in a table or all the values in a column, ignoring any filters which might have been applied.
Syntax- ALL ([<TableNameOrColumnName>] [, <ColumnName> [, <ColumnName> [, …]]]

This syntax will help us to get a total of sales value while ignoring any filters which might have been applied on the product name. In the image shown below we have a measure named as “ALL” present in the table which is providing a total of sales value without getting affecting through the product name column present in the table, as we have used the “ALL” function for that particular column. The output is shown below-

ALLSELECTED- Returns all the rows in a table or all the values in a column, ignoring any filters which might have been applied on the columns or the rows but keeping all other explicit filters.

Syntax- ALLSELECTED([<tableName> | <columnName> [, <columnName> [, <columnName> [,]]]])

This syntax will help us track down the sales value considering the explicit filters applied but ignoring any filters applied on rows or columns. And with the image below it can be evident that the “ALLSELECTED” measure is taking into account the explicit filter applied and providing the output.

A) Table view is representing that the “ALLSELECTED” measure sales total is not affected by the product name column present in table

B) This Table View is representing that only “Product Name” explicit filter mentioned in the ALLSELECTED measure will affect its total.

ALLEXCEPT- Returns all the rows in a table or all the values in a column, ignoring all context filters applied but taking into account the specified columns filter.

Syntax- ALLEXCEPT(<table>, <column> [, <column> [,]]) 

This particular syntax will help in calculating sales value while considering the explicit filters applied for the specified column that is “Product name” but other than that no explicit filter will work for this. Result can be seen below-

A)  Table view represents that the “Product Name” explicit filter cannot affect the ALLEXCEPT total sales.

B) Table view represents that except the “Category” explicit filter no other will have an effect on ALLEXCEPT total

Rajeshwari Sharma
Data Analyst
Addend Analytics