Excel DAX - Filter Functions

Advertisements

Previous Page
Next Page  

DAX has powerful filter functions that are quite different from Excel functions. The lookup functions work by using tables and relationships, like a database. The filtering functions let you manipulate data context to create dynamic calculations.

Note − DAX filter functions that return a table do not add the table to the Data Model. The resulting table is used as an argument in another DAX function. That is, such DAX functions are used as nested functions with other DAX functions.

In the next section, you will learn what DAX filter functions you can use. For more details on these functions, refer to the tutorial – DAX Functions in this tutorials library.

DAX Filter Functions

Following are the DAX Filter functions −

Sr.No. DAX Function & What the Function Does?
1

ADDMISSINGITEMS (<showAllColumn>, [<showAllColumn>] …, <table>, <groupingColumn>, [<groupingColumn>] …, [filterTable] …)

Adds combinations of items from multiple columns to a table if they do not already exist. The determination of which item combinations to add is based on referencing source columns which contain all the possible values for the columns.

To determine the combinations of items from different columns to evaluate −

  • AutoExist is applied for columns within the same table.
  • CrossJoin is applied across different tables.
2

ALL ( {<table> | <column>, [<column>], [<column>], … })

Returns all the rows in the given table or all the values in the specified columns in a table, 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.
3

ALLEXCEPT (<table>, <column>, [<column>], …)

Removes all context filters in the table except filters that have been applied to the columns specified as arguments.

As against ALL, you can use this function when you want to remove the filters on many, but not all, columns in a table.
4

ALLNOBLANKROW (<table>|<column>)

From the parent table of a relationship, returns −

  • all rows but the blank rows, or
  • all distinct values of a column but the blank rows

The function disregards any context filters that might exist.
5

ALLSELECTED ( [<tableName>|<columnName>] )

Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.
6

CALCULATE (<expression>, [<filter1>, <filter2> …)]

Evaluates an expression in a context that is modified by the specified filters.

Returns the value that is the result of the expression.
7

CALCULATETABLE (<expression>, <filter1>, <filter2>, …)

Evaluates a table expression in a context modified by the given filters.

Returns a table of values.
8

CROSSFILTER (<columnName1>, <columnName2>, <direction>)

Specifies the cross-filtering direction to be used in a calculation for a relationship that exists between two columns. Does not return any value.
9

DISTINCT (<column>)

Returns a one-column table that contains the distinct values from the specified column. In other words, duplicate values are removed and only unique values are returned.

The resulting column is used as an argument in another DAX function.
10

EARLIER (<column>, <number>)

Returns the current value of the specified column in an outer evaluation pass of the mentioned column specified by the number.
11

EARLIEST (<column>)

Returns the current value of the specified column in an outer evaluation pass of the specified column.
12

FILTER (<table>, <filter>)

Returns a table that contains only the filtered rows.

FILTER is used only as a function that is embedded in other functions that require a table as an argument.
13

FILTERS (<columnName>)

Returns the values that are directly applied as filters to columnName.

FILTERS is used only as a function that is embedded in other functions that require a table as an argument.
14

HASONEFILTER (<columnName>)

Returns TRUE when the number of directly filtered values on columnName is one. Otherwise, returns FALSE.
15

HASONEVALUE (<columnName>)

Returns TRUE when the context for columnName has been filtered down to one distinct value only. Otherwise, returns FALSE.
16

ISCROSSFILTERED (<columnName>)

Returns TRUE when columnName or another column in the same or related table is being filtered.
17

ISFILTERED (<columnName>)

Returns TRUE when columnName is being filtered directly. If there is no filter on the column or if the filtering happens because a different column in the same table or in a related table is being filtered, then the function returns FALSE.
18

KEEPFILTERS (<expression>)

Modifies how filters are applied while evaluating a CALCULATE or CALCULATETABLE function.
19

RELATED (<column>)

Returns a related value from another table.
20

RELATEDTABLE (<tableName>)

Evaluates a table expression in a context modified by the given filters.
21

SUBSTITUTEWITHINDEX (<table>, <indexColumnName>, <indexColumnTable>, <orderBy_expression>, [<order>])

Returns a table which represents a left semijoin of the two tables supplied as arguments.

The semijoin is performed by using common columns, determined by common column names and common data type.

The columns being joined on are replaced with a single column in the returned table which is of type integer and contains an index.

The index is a reference into the right join table given a specified sort order.
22

USERELATIONSHIP ( <columnName1>,<columnName2>)

Specifies the relationship to be used in a specific calculation as the one that exists between columnName1 and columnName2.
23

VALUES (<TableNameOrColumnName>)

Returns a one-column table that contains the distinct values from the specified table or column.

In other words, duplicate values are removed and only unique values are returned.
Advertisements