Filter Functions
Filter functions manipulate table and filter contexts.
| Function | Description |
|---|---|
| ALL | Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. |
| ALLCROSSFILTERED | Clear all filters which are applied to the specified table. |
| ALLEXCEPT | Returns all the rows in a table except for those rows that are affected by the specified column filters. |
| ALLNOBLANKROW | Returns all the rows except blank row in a table, or all the values in a column, ignoring any filters that might have been applied. |
| ALLSELECTED | Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied inside the query, but keeping filters that come from outside. |
| CALCULATE | Evaluates an expression in a context modified by filters. |
| CALCULATETABLE | Evaluates a table expression in a context modified by filters. |
| FILTER | Returns a table that has been filtered. |
| INDEX | Retrieves a row at an absolute position (specified by the position parameter) within the specified partition sorted by the specified order or on the axis specified. |
| KEEPFILTERS | Changes the CALCULATE and CALCULATETABLE function filtering semantics. |
| LOOKUP | Identifies cells where specified columns match evaluated expressions, and retrieves a column value (only when a single cell is identified) or calculates an expression's value from those matching cells. Filter context from columns not specified will be applied implicitly. |
| LOOKUPVALUE | Retrieves a value from a table. |
| LOOKUPWITHTOTALS | Identifies cells where specified columns match evaluated expressions, and retrieves a column value (only when a single cell is identified) or calculates an expression's value from those matching cells. LOOKUPWITHTOTALS defaults the non-specified columns to the total, whereas LOOKUP does not change the filter over non-specified columns. |
| MATCHBY | The columns used to determine how to match data and identify the current row. Can only be used within a Window function. |
| OFFSET | Retrieves a single row from a relation by moving a number of rows within the specified partition, sorted by the specified order or on the axis specified. |
| ORDERBY | The expressions and order directions used to determine the sort order within each partition. Can only be used within a Window function. |
| PARTITIONBY | The columns used to determine how to partition the data. Can only be used within a Window function. |
| REMOVEFILTERS | Clear filters from the specified tables or columns. |
| SELECTEDVALUE | Returns the value when there's only one value in the specified column, otherwise returns the alternate result. |
| WINDOW | Retrieves a range of rows within the specified partition, sorted by the specified order or on the axis specified. |
Last update: Jul 2, 2025 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Ville-Pietari Louhiala
2018-2025 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us » Privacy Policy & Cookies