ALL Filter: The ALL filter 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.
REMOVEFILTERS: Introduced in 2019 after the ALL filter, REMOVEFILTERS clears filters from specified tables or columns. However, there are substantial differences between these two functions, which we will discuss shortly.
// Removes filters from the specified table or column. Can be used to return all rows in a table or all values in a column, ignoring any filters that may be applied.
ALL_measure = CALCULATE(
DISTINCTCOUNT('Sales by Store'[product_id]),
ALL('Sales by Store')
)
REMOVEFILTERS = CALCULATE(
sum('Sales by Store'[Total Revenue]),
REMOVEFILTERS('Sales by Store')
)
--both work same
--If apply any filter like store_ID = 2 then ALL_measure not affected| Region | Amount |
|---------|--------|
| North | 100 |
| South | 200 |
| East | 150 |
| West | 250 |
--Suppose you have applied a filter to show only the North region.
TotalSalesAllRegions = CALCULATE(SUM(Sales[Amount]), ALL(Sales))
| Region | Filtered Amount | TotalSalesAllRegions |
|--------|-----------------|----------------------|
| North | 100 | 700 |
Difference between ALL and REMOVEFILTERS:
While both functions remove filters, there is a significant difference in their behavior:
- ALL: Removes filters and returns a table or a column, which can be used in other DAX functions like CALCULATE and SUMX. This makes it possible to create complex measures and calculated columns based on unfiltered data.
- REMOVEFILTERS: This function does not return a table or a column; it only removes filters from the specified table(s) or column(s). You can use it within a CALCULATE function to remove filters, but unlike ALL, it cannot be used in conjunction with other DAX functions like SUMX.
- KEEPFILTERS:
| Category | Region | Amount |
|----------|----------|--------|
| A | North | 100 |
| B | North | 200 |
| A | South | 300 |
| C | East | 400 |
| A | East | 500 |
| B | South | 600 |
| A | North | 700 |
SalesCategoryA = CALCULATE(SUM(Sales[Amount]), KEEPFILTERS(Sales[Category] = "A"))
FilterBycat_A= CALCULATE(SUM(Sales[Amount]), Sales[Category] = "A")
| Category | Region | Amount | SalesCategoryA |FilterBycat_A |
|----------|----------|--------|----------------|-------------- |
| A | North | 100 | 800 |800 |
| B | North | 200 | (Blank) |800 |
| A | South | 300 | 300 |300 |
| C | East | 400 | (Blank) |500 |
| A | East | 500 | 500 |500 |
| B | South | 600 | (Blank) |300 |
| A | North | 700 | 800 |800 |
-- SalesCategoryA or FilterBycat_A for Category = "A" never change value for any Category but change value for another filter.| Function | Description | Syntax | Example | Sample Output |
|---|---|---|---|---|
| ALL() | Removes all filters from the specified table or column. | =ALL(TableOrColumn) | =CALCULATE(SUM(Sales), ALL(Sales)) | Ignores filters on Sales for the calculation of total Sales. |
| REMOVEFILTERS() | Removes filters from the specified table or column. Similar to ALL(). | =REMOVEFILTERS(TableOrColumn) | =CALCULATE(SUM(Sales), REMOVEFILTERS(Sales)) | Removes filters on Sales for the calculation of total Sales. |
| KEEPFILTERS() | Adds an additional filter to the calculation without removing existing filters. | =CALCULATE(Expression, KEEPFILTERS(TableOrColumn)) | =CALCULATE(SUM(Sales), KEEPFILTERS(Sales = "A")) | Adds filter Sales = "A" and keeps other existing filters. |
| ALLEXCEPT() | Removes all filters in the table except the specified columns. | =ALLEXCEPT(Table, Column1, Column2, ...) | =CALCULATE(SUM(Sales), ALLEXCEPT(Sales, Sales)) | Removes all filters from Sales except those on Sales. |
| ALLSELECTED() | Returns all the rows in the table, or all the values in the column, with all the filters that have been applied. | =ALLSELECTED(TableOrColumn) | =CALCULATE(SUM(Sales), ALLSELECTED(Sales)) | Considers all filters on Sales that have been applied by user selection. |
| ALLCROSSFILTERED() | Removes filters from columns and tables that are cross-filtered. | =ALLCROSSFILTERED(Column) | =CALCULATE(SUM(Sales), ALLCROSSFILTERED(Sales)) | Ignores cross-filters on Sales for the calculation of total Sales. |
| FILTER() | Returns a table that represents a subset of another table or expression. | =FILTER(Table, Expression) | =FILTER(Sales, Sales > 200) | Returns a table of Sales rows where Sales is greater than 200. |
| VALUES() | Return a Distinct value with blank (include blank if any other table have extra value ) ex. Product table have 3 group X,Y,Z and Sale table have product group X,Y,Z,A values(product) Output: X,Y,Z,Blank | =VALUES(Column) | =VALUES(Sales) | Returns a table of distinct Sales values. |
| RELATED() | Returns a related value from another table. | =RELATED(Column) | =RELATED(Product) | Returns the ProductName from the Product table related to the current row. |
| RELATEDTABLE() | Returns a table related to the current table. | =RELATEDTABLE(Table) | =RELATEDTABLE(Sales) | Returns all rows from Sales related to the current table's row. |
| DISTINCT() | Return a distinct value or distinct dataset | =DISTINCT(Column) | =DISTINCT(Sales) | Returns a table of distinct Sales values. |
| SELECTEDVALUE() | Returns the value when the context for columnName has been filtered down. | =SELECTEDVALUE(Column, ) | =SELECTEDVALUE(Sales, "Multiple") | Returns the selected Sales value, or "Multiple" if there are multiple values. |