- FILTER is a very commonly used function in DAX.
It is a table function that takes
a table as its first argument
and returns only the row
that satisfy a condition
that you specify as the second argument.
FILTER is an iterator.
So it scans the first argument row by row
and evaluates the condition in a row context created
during the iteration.
Let's see some example of its usage.
In this first example, I'm iterating
over customer, so I'm using FILTER over customer,
retrieving only the rows
where the customer continent equals Europe.
The result contains all the columns of the customer table
where the continent equals Europe.
The condition customer continent equals Europe
is evaluated in the row context
that FILTER generates during the iteration.
Because FILTER is an iterator
and its condition is evaluated in a row context,
if you need to access columns,
which are in a different table
that shares a relationship
with the table currently being iterated,
you need to use RELATED.
So here we have SUMX that iterates FILTER.
FILTER iterates sales
but because we want to place the condition
on the color column, which is in the product table,
we need to use RELATED
in order to access the value of the color
and check that its value is red.
You can obtain a similar behavior,
actually the same behavior by using CALCULATE.
Using CALCULATE because CALCULATE
does not rely on row context
but works with the filter context,
you do not have to worry
about RELATED and you can place the filter
on the product color, relying on the fact
that the filter context
is automatically propagated to the table.
With that said, FILTER is extremely useful
whenever you want to rely on the row context.
If, for example, you want to filter based
on a measure, then you need context transition to happen.
And this is exactly what we are doing here.
You define a MEASURE sales amount
that computes just the sales amount
and then you want to compute the sales amount
for only the country region
where the sales amount is greater than three million.
So you need to use FILTER ALL customer country region
that retrieves a table containing all the country region.
FILTER generates a row context during the iteration
over this table and sales amount being a measure
is automatically surrounded by CALCULATE.
So context transition is happening
and you can place a condition over a measure
during the iteration.
FILTER now returns all the country region
where the sales are more than three million
and the sales amount will be computed
into that filter context.
Another scenario where it is useful
to use FILTER, actually it is needed to use FILTER
is whenever you need to iterate over a variable
and... evaluate conditions over that variable.
In this code, for example,
I'm creating monthly sales,
which is a temporary table containing the year month
and the sales amount in the column monthly sales,
which I generate during the query.
If I then need to apply a filter on this column,
what I need to do is use FILTER.
I cannot use CALCULATE in such a case
because the column does not belong to the model.
Therefore, it does not have data lineage
and CALCULATE is not able to place a filter on it.
FILTER, on the other hand, relying on the iteration
can scan the monthly sales variable
and retrieve only the rows where monthly sales
is greater than 10,000.
After that, I can go on and perform other calculations.
So you use FILTER whenever you need
to rely on its nature as an iterator
that generates a row context.