DAX functions are thriving nowadays, they have become one of the most popular and learned languages in the Business Intelligence Domain. It contains expressions that 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