FILTER DAX
FUNCTIONS
-By Anish Chakravorty
OVERVIEW
Filter functions are essential in DAX (Data Analysis Expressions)
to manipulate table and filter contexts within calculations. They
allow you to refine the data used in measures, calculated
columns, and tables by applying specific conditions.
FILTER FUNCTIONS
CALCULATE FILTER ALL ALLEXCEPT
Modifies the filter Returns a table with Removes all filters from Removes all filters
context of a measure or rows that meet a a specified column or except those on
expression. Use Case is specified condition. Use table. Use case is Useful specified columns. Use
Used to evaluate an case is Used to apply for calculating totals or Case is when you want
expression in a row-level filters on a percentages that ignore to keep filters on
modified filter context, table. existing filters. specific columns while
such as calculating ignoring others.
total sales for a specific
Syntax: FILTER(<Table>, Syntax:
product category.
<Condition>) ALL(<Table/Column>) Syntax:
ALLEXCEPT(<Table>,
Syntax:
<Column1>, <Column2>,
CALCULATE(<Expressio
n>, <Filter1>, <Filter2>, ...) ...)
FILTER FUNCTIONS
ALLSELECTED KEEPFILTERS REMOVEFILTERS SELECTEDVALUE
Returns all selected Ensures that the Removes filters from the Returns the value of a
rows in the current original filter context is specified columns or column if only one value
filter context, ignoring maintained in addition tables. Use case is when is selected; otherwise,
filters from outside the to new filters. Use case you want to override returns an alternate
report (e.g., slicers).Use is when you want to filters applied elsewhere result. Use case is used
case is Helpful in add new filters but also in the report. in situations where
situations where you keep existing ones slicers or filters might
want to evaluate only return multiple values.
intact. Syntax:
selected values while
REMOVEFILTERS(<Table
ignoring filters applied
Syntax: /Column>) Syntax:
externally.
KEEPFILTERS(<Filter>) SELECTEDVALUE(<Colu
Syntax: mn>, [AlternateResult])
ALLSELECTED(<Table/C
olumn>)
FILTER DAX FUNCTIONS
EXAMPLES
Some basic examples for filter dax functions to give a good overview for this Dax
functions.
FILTER ALLEXCEPT KEEPFILTERS SELECTEDVALUE
CALCULATE(SUM(Sales[T SELECTEDVALUE(Sales[Pro
FILTER(Sales, ALLEXCEPT(Sales,
otal Sales]), duct Category], "No
Sales[Quantity] > 10) Sales[Region]) →
KEEPFILTERS(Sales[Prod Category Selected") →
→ Returns a table of Removes all filters
uct Category] = Returns the selected
sales where quantity except the filter on
"Electronics")) → Applies product category, or "No
is greater than 10. Region.
a filter for Electronics Category Selected" if none
without removing or multiple categories are
existing filters. selected.
CALCULATE ALL ALLSELECTED REMOVEFILTERS
CALCULATE(SUM(Sales[ CALCULATE(SUM(Sales[ CALCULATE(SUM(Sales[
ALLSELECTED(Sales)
Total Sales]), Total Sales]), Total Sales]),
→ Returns all selected
Sales[Region] = "West") ALL(Sales[Product REMOVEFILTERS(Sales[
rows from the Sales
→ Returns total sales Category])) → Returns Region])) → Returns
table based on
only for the West total sales ignoring total sales after
current slicer
region. filters on product removing all filters on
selections.
categories. the Region column.
BEST PRACTICES FOR TEXT
FUNCTIONS
MINIMIZE FILTER CONTEXT CONFLICTS COMBINE FILTERS FOR FLEXIBILITY
01 Use functions like REMOVEFILTERS and
ALL judiciously to ensure calculations are
not accidentally affected by external
03 Use CALCULATE in conjunction with
functions like KEEPFILTERS and ALLEXCEPT
to apply complex, dynamic filter conditions
filters. without overwriting existing filters.
USE SELECTEDVALUE IN CONDITIONAL EFFICIENT CALCULATIONS
FORMATTING
02 04
Avoid unnecessary calculations by restricting
When creating visualizations, use filters only to the relevant columns using
SELECTEDVALUE to display selected ALLEXCEPT or ALLSELECTED, rather than
slicer or filter values within titles or clearing all filters with ALL.
labels.
THANK YOU
