Dax Function: Filter Functions
Category: Filter Functions
Filter functions in Power BI’s DAX (Data Analysis Expressions) language are used to create, apply, and manipulate filters on tables and columns. These functions allow for dynamic filtering of data based on conditions or context, which is essential for creating insightful and targeted reports.
Purpose
The primary purpose of filter functions is to refine datasets by specifying criteria. This enables users to work with subsets of data that meet specific conditions, improving analytical precision and reducing noise in visualizations or calculations.
| Function | Description |
|---|---|
| ALL | Retrieves all rows from a table or all unique values from a column, ignoring any applied filters. |
| ALLCROSSFILTERED | Clears all cross-filters applied to a table. |
| ALLEXCEPT | Removes all context filters on a table except those applied to specified columns. |
| ALLNOBLANKROW | Returns all rows or distinct values from a table or column, excluding blank rows, while ignoring context filters. |
| ALLSELECTED | Removes context filters applied in the current query, while retaining explicit and other context filters. |
| CALCULATE | Computes an expression in a modified filter context. |
| CALCULATETABLE | Evaluates a table expression in a modified filter context. |
| EARLIER | Returns the value of a specified column from an outer evaluation pass. |
| EARLIEST | Similar to EARLIER, retrieves the value of a column from the earliest outer evaluation pass. |
| FILTER | Produces a subset of a table based on a condition or expression. |
| FIRSTNONBLANK | Returns the first value in a column where an expression is not blank. |
| FIRSTNONBLANKVALUE | Evaluates an expression for the first value in a column where the result is not blank. |
| FIRST | For visuals, retrieves the first row’s value from an axis in the visual matrix. |
| INDEX | Fetches a row by its absolute position within a partition, based on sorting criteria or axis order. |
| KEEPFILTERS | Alters how filters are maintained during the evaluation of CALCULATE or CALCULATETABLE functions. |
| LASTNONBLANK | Returns the last value in a column where an expression is not blank. |
| LASTNONBLANKVALUE | Evaluates an expression for the last value in a column where the result is not blank. |
| LAST | For visuals, retrieves the last row’s value from an axis in the visual matrix. |
| LOOKUP | For visual calculations, retrieves a value based on applied filters. |
| LOOKUPWITHTOTALS | For visual calculations, looks up a value with applied filters, ignoring unspecified filters. |
| LOOKUPVALUE | Fetches the value that matches all specified search conditions for a row. |
| MATCHBY | In window functions, specifies columns used to match data and identify the current row. |
| MOVINGAVERAGE | Computes a moving average along a specified axis in the visual matrix. |
| NEXT | For visuals, retrieves the value from the next row on the axis in the visual matrix. |
| OFFSET | Retrieves a row located a specified number of positions before or after the current row in the same table. |
| ORDERBY | Defines columns that determine the sorting order within partitions of a window function. |
| PARTITIONBY | Specifies columns that define partitions for a window function. |
| PREVIOUS | For visuals, retrieves the value from the previous row on the axis in the visual matrix. |
| RANGE | Fetches a range of rows relative to the current row along a specified axis; an alias for WINDOW. |
| RANK | Calculates the rank of a row within a defined interval. |
| REMOVEFILTERS | Removes filters from specified columns or tables. |
| ROWNUMBER | Assigns a unique number to each row based on its ranking within a specified interval. |
| RUNNINGSUM | Computes a cumulative sum along a specified axis in the visual matrix. |
| SELECTEDVALUE | Returns a value when a column is filtered to one distinct value; otherwise, returns an alternate result. |
| WINDOW | Produces multiple rows located within a defined range relative to the current row. |
Maximize the potential of Power BI and enhance your data insights with our expert consulting services. Whether you’re looking for assistance with advanced DAX functions, help designing interactive dashboards, or support in optimizing your data models for better performance, our skilled Power BI consultants are ready to provide tailored solutions for your business. Visit our Power BI consultancy page to learn more about how we can empower your organization to make more informed, data-driven decisions.
Filter functions are DAX expressions that allow users to filter and refine data based on specified criteria.
No, DAX operates in a read-only mode. Filter functions create filtered views or subsets of data without changing the original dataset.
CALCULATE modifies the context of an expression by applying filters, while FILTER returns a table containing rows that meet a condition.
Yes, with functions like TREATAS, filters can be applied across unrelated tables.
Using complex filters on large datasets can affect performance. Optimize by applying filters early in the data pipeline and avoiding resource-intensive functions.