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

Best Regards
Anmol malviya

Data Analyst | Addend Analytics

Data Labels are one of the critical components of any Power BI visual. It allows users to understand the visuals easily. With the help of Custom Data Labels, users can see a particular data point and trends.

Does your application have a REST API with token-based pagination, and you're unsure how to make it work in Power Query/M Language? Dive into this article to learn how to unlock insights effortlessly!

Discount certification voucher offer for Fabric Data Engineer (Exam DP-700).

Live learning sessions starting Jan 28th.