 Excel DAX Tutorial
 DAX  Home
 DAX  Overview
 DAX  Calculated Columns
 DAX  Calculated Fields / Measures
 DAX  Editing a Calculated Field
 DAX  Deleting a Calculated Field
 DAX  Syntax
 DAX  Operators
 DAX  Standard Parameters
 DAX  Functions
 DAX  Understanding DAX Functions
 DAX  Evaluation Context
 DAX  Formulas
 Updating Results of DAX Formulas
 Updating Data in the Data Model
 DAX  Recalculating DAX Formulas
 Troubleshooting DAX Formula Recalculation
 DAX  Formula Errors
 DAX  Time Intelligence
 DAX  Filter Functions
 DAX  Scenarios
 Performing Complex Calculations
 DAX  Working with Text and Dates
 Conditional Values & Testing for Errors
 DAX  Using Time Intelligence
 DAX  Ranking & Comparing Values
 Excel DAX Useful Resources
 DAX  Quick Guide
 DAX  Useful Resources
 DAX  Discussion
Excel DAX  Filter Functions
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 −

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 −
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 crossfiltering 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 onecolumn 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 onecolumn 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. 