The FILTER function returns a table that represents a subset of another table.
FILTER(
table,
filter
)
- table: Table to be filtered or expression that returns a table.
- filter: Boolean expression that will be evaluated for each row of the table.
The FILTER function returns a table made up of those rows from the original table that have passed the filter.
This function is often used as an argument in other functions (mainly in the CALCULATE function) when a table needs to be filtered.
Suppose we have defined the measure
Ventas = SUM(FactSales[SalesAmount])
...which adds up the sales amounts. Now imagine that we want to add the sales of those products whose price is higher than the average value of the sales prices. To do this, our first attempt would be to define the following measure:
Ventas por encima del promedio = CALCULATE(
[Ventas],
DimProduct[UnitPrice]>AVERAGE(DimProduct[UnitPrice])
)
...measure that, in any case, causes an error because CALCULATE does not admit complex expressions like this as an argument:
The solution is to add as an argument not the complex expression in question, but the sales table once we have filtered it according to the same criteria:
Ventas por encima del promedio = CALCULATE(
[Ventas],
FILTER(
DimProduct,
DimProduct[UnitPrice]>AVERAGE(DimProduct[UnitPrice])
)
)
...which returns the result we were looking for.
- Existence of records with a certain value in a field
- Records for which there is no associated field in a remote table
- Using variables in DAX expressions
- Creating a table with average sales per salesperson
- Calculation of sales prior to a given date
- Viewing the days of occupation of a resource
- Calculation of the number of days between purchases
- TOTALYTD function emulation
- Calculation of total sales of the country to which each city belongs
- Calculation of the previous purchase date for each purchase of each customer
- Calculation of the number of values prior to each date
