Introduction
The filter functions in DAX are very useful and complex, and they are very different from Microsoft Excel functions. The query functions work by utilizing tables and connections, similar to an information base. The sifting capacities let you control information setting to make dynamic estimations.
In this article, I will explain all Filter functions that are used in Power BI. Below is the list of these functions.
Functions and Their Description
ALL
Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.
Syntax- ALL([TableNameOrColumnName], [ColumnName1],….)
TableNameOrColumnName - The table that you want to clear filters on.
ColumnName- The Column that you want to clear filters on.
Ex.
ALLEXCEPT
Returns all the rows in a table except for those rows that are affected by the specified column filters.
Syntax - ALLEXCEPT (TableName, ColumnName)
TableName - The table over which all context filters are removed, except filters on those columns that are specified in subsequent arguments.
ColumnName - The column for which context filters must be preserved.
Ex.
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.
Syntax- ALLSELECTED([TableNameOrCOlumnName], [ColumnName1], [ColumnName2],…)
TableNameOrColumnName –This is optional.The name of existing table, using DAX syntax. The parameter cannot be an expression.
ColumnName-This is optional. The name of an existing column using standard DAX syntax, usually fully qualified. It cannot be an expression.
Ex.
CALCULATETABLE
Evaluates a table expression in a context modified by filters.
Syntax- CALCULATETABLE(Table, [Filters1],….)
Table -The table expression to be evaluated.
Filters -(Optional) Boolean expressions or table expressions that defines filters, or filter modifier functions.
Ex.
EARLIER
Returns the value in the column prior to the specified number of table scans (default is 1).
Syntax- EARLIER(ColumnName, [Number])
ColumnName- A column or expression that resolves to a column.
Number- It is Optional. A positive number to the outer evaluation pass.
EARLIEST
Returns the value in the column for the very first point at which there was a row context.
Syntax- EARLIEST(ColumName)
COlumnName- Areference to a column.
FILTER
Returns a table that has been filtered.
Syntax - FILTER (Table, FilterExpression)
Table -The table to be filtered. The table can also be an expression that results in a table.
FilterExpression - A Boolean expression that is to be evaluated for each row of the table. For example, [Number] >100 or [City] = "Delhi"
KEEPFILTERS
Changes the CALCULATE and CALCULATABLE function filtering semantics.
Syntax- KEEPFILTERS (Expression)
Expression- Any expression.
Note - This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
LOOKUPVALUE
Retrieve a value from a table.
Syntax- LOOKUPVALUE(Result_ColumnName, Search_ColumnName1, Search_Value1,…..,[Alternate_Result])
Result_ColumnName-The name of an existing column that contains the value you want to return. It cannot be an expression.
Search_ColumnName1- The name of an existing column. It can be in the same table as result_columnName or in a related table. It cannot be an expression.
Search_Value- Any expression.
Alternate_Result –The value returned when the context for result_columnName has been filtered down to zero or more than one distinct value.
REMOVEFILTER
Clear filter from the specified tables or columns.
Syntax- REMOVEFILTERS([TableNameOrColumnName], [ColumnName1],…)
TableNameOrColumnName - The table that you want to clear filters on.
ColumnName - The column that you want to clear filters on.
SELECTEDVALUE
Returns the value when there’s only one value in the specified column, otherwise returns the alternate result.
Syntax - SELECTVALUE (ColumnName, [AlternateResult])
ColumnName -The name of an existing column, using standard DAX syntax. It cannot be an expression
AlternateResult - It is optional. The value returned when the context for columnName has been filtered down to zero or more than one distinct value.
Summary
These are some filter functions that are commonly used in Power BI. Hope you understand and try to use them in real scenarios. Thanks for reading. Have a nice day.