Filter Functions

The filter and value functions in DAX are some of the most complex and powerful, and differ greatly 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.

In this category

FunctionDescription
ADDMISSINGITEMSAdds combinations of items from multiple columns to a table if they do not already exist.
ALLReturns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.
ALLCROSSFILTEREDClear all filters which are applied to a table.
ALLEXCEPTRemoves all context filters in the table except filters that have been applied to the specified columns.
ALLNOBLANKROWFrom the parent table of a relationship, returns all rows but the blank row, or all distinct values of a column but the blank row, and disregards any context filters that might exist.
ALLSELECTEDRemoves context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.
CALCULATEEvaluates an expression in a context that is modified by the specified filters.
CALCULATETABLEEvaluates a table expression in a context modified by the given filters.
CROSSFILTERSpecifies the cross-filtering direction to be used in a calculation for a relationship that exists between two columns.
DISTINCTReturns a one-column table that contains the distinct values from the specified column.
EARLIERReturns the current value of the specified column in an outer evaluation pass of the mentioned column.
EARLIESTReturns the current value of the specified column in an outer evaluation pass of the specified column.
FILTERReturns a table that represents a subset of another table or expression.
FILTERSReturns the values that are directly applied as filters to columnName.
HASONEFILTERReturns TRUE when the number of directly filtered values on columnName is one; otherwise returns FALSE.
HASONEVALUEReturns TRUE when the context for columnName has been filtered down to one distinct value only. Otherwise is FALSE.
ISCROSSFILTEREDReturns TRUE when columnName or another column in the same or related table is being filtered.
ISFILTEREDReturns TRUE when columnName is being filtered directly.
KEEPFILTERSModifies how filters are applied while evaluating a CALCULATE or CALCULATETABLE function.
RELATEDReturns a related value from another table.
RELATEDTABLEEvaluates a table expression in a context modified by the given filters.
REMOVEFILTERSClears filters from the specified tables or columns.
SELECTEDVALUEReturns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult.
SUBSTITUTEWITHINDEXReturns a table which represents a left semijoin of the two tables supplied as arguments.
USERELATIONSHIPSpecifies the relationship to be used in a specific calculation as the one that exists between columnName1 and columnName2.
VALUESReturns a one-column table that contains the distinct values from the specified table or column.

***** Related Blog Posts *****
60 Page DAX Formula Reference Guide Download Now Available
Show Only Top Or Bottom Results Using Ranking Formula Techniques – Power BI & DAX

Using Filter Fields & Visual Interactions To Create Compelling Visualizations In Power BI
ALL Function in Power BI – How To Use It With DAX

***** Related Video Tutorials *****
Ultimate Beginners Guide to DAX

What Is DAX? & Why Do You Need To Learn It – (1.2) Ultimate Beginner Guide to DAX
Formula Syntax, Comments & Variables – (1.6) Ultimate Beginners Guide to DAX
Examples Of Advanced DAX – (1.18) Ultimate Beginners Guide to DAX

***** Related Course Modules *****
Ultimate Beginners Guide to DAX
Mastering DAX Calculations
Solving Analytical Scenarios w/ Power BI and DAX
DAX Formula Deep Dives
Advanced DAX Combinations

***** Related Support Forum Posts*****
Mastering DAX Calculations: What Is DAX?
Understanding DAX Formula
Developing Complex Dax functions

Center of Excellence

DOWNLOAD


Download our comprehensive DAX Formula Reference Guide as perfect companion as you learn how to use DAX formulas within Power BI.


Download DAX Formulas Reference Guide
Download DAX Formula Reference Guide
Membership