ALL Vs ALLEXCEPT Vs ALLSELECTED in Power BI DAX
ALL, ALLEXCEPT, ALLSELECTED are DAX filter functions that return the rows in a table, or all the values in a column with ignoring or keeping filters, and let you manipulate data context to create dynamic calculations.
1) ALL DAX Filter Function
ALL() is a DAX Filter Function that returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.
- ALL() function is useful for clearing filters and creating calculations on all the rows in a table.
- ALL() function is not used by itself but serves as an intermediate function that can be used to change the set of results over which some other calculation is performed.
- This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
ALL DAX Filter Function Example
You can use ALL() DAX function to remove all table filters or a specific column in the table
1) Remove All Table Filter Examples
= SUMX(Table1, Table1[Field1])/SUMX(ALL(Table1), Table1[Field1])
In the example, the ALL function removes all context filters on the Table1 and gets the sum of Field1
2) Remove Filter for a specific column using ALL()
= SUMX(Table1, Table1[Field1])/CALCULATE( SUM( Table1[Field1]), ALL(DateTime[Field2]))
In this example, remove the filter on Field2, but keep the other filter columns.
Read more at ALL DAX Filter Function
2) ALLEXCEPT DAX Filter Function
ALLEXCEPT() is a DAX Filter Function that removes all context filters in the table except filters that have been applied to the specified columns.
- This function is not used by itself but serves as an intermediate function that can be used to change the set of results over which some other calculation is performed.
- This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
1) Keep Filter for a specific column using ALLEXCEPT()
= CALCULATE(SUM(Table1[SalesAmount]), ALLEXCEPT(DateTime, DateTime[CalendarYear]))
In this example, you use the ALLEXCEPT function to remove any context filters on the DateTime table except if the filter has been applied to the CalendarYear column.
Read more at ALLEXCEPT DAX Filter Function
3) ALLSELECTD DAX Filter Function
ALLSELECTD() is another Filter function that is used to return all the rows in a table, or all the values in a column, ignoring any filters that may have been applied inside the query, but keeping filters that come from the outside.
The main difference between ALL and ALLSELECTED in DAX is
- The ALL function ignores all filters, regardless of where they are coming from.
- The ALLSELECTED function only ignores filters that are coming from the inner query but keeps the slicer and report filters.
Example
= CALCULATE(Table1[SalesAmount], ALLSELECTED(DateTime[CalendarYear]))
In this example, you need to calculate all sales amounts but keep a filter for selected dates in the slicer or any other filters.
Read more at ALLSELECTD DAX Filter Function