FILTER() is a part of our every Power BI DAX Essentials training. The function derives its functionality after its name. The FILTER() function is used to slice the data based on certain conditions. The resulting table is a subset of the original table containing only the rows that fulfill the given condition. The resulting table can also be a virtual table that can be used for iteration by other functions.

Let’s create a table where only the products that fall in the category of “Accessories”. To use FILTER() for this:

1. Click on the Modeling tab in the Ribbon.
2. Click on New Table.
3. Type the following expression in the formula bar and press Enter.

In this Expression:

• Only the products from the category of “Accessories” are filtered.

4. Click on the Data tab in the left Ribbon.
5. Click on Testing Table Functions using FILTER (CT) in the Fields pan.

Notice that the new table contains only the product details for accessories. Please note that you can format any column (such as Standard Cost and List Price) of your choice using the formatting section.

Filter Using Multiple Conditions:

Now let’s create a table containing only those accessories that have the list price greater than 30.

6. Click on the Modeling tab in the Ribbon.
7. Click on New Table.
8. Type the following expression in the formula bar and press Enter.

In this Expression:

• Only the products from the category of “Accessories” that have a list price greater than 30 are filtered.

9. Click on the Data tab in the left Ribbon.
10. Click on Testing Table Functions using FILTER (CT) in the Fields pan.

Notice that the resulting table contains only those accessories that have the list price greater than 30.

Virtual table lineage

Virtual table lineage is the link retained by any virtual table to the original table. For example, consider the DAX code shown below:

In the formula above, it may look like FILTER() pulls the data from the Customers table and functions in a standalone manner. This is however not the case, all table functions maintain a link to the original table and this is called lineage. This means that as soon as the Customer table is updated, the table maintaining lineage with it is also updated.

The imaginary table is considered a part of data model but it is never materialized or physically created.

 Conclusion:

FILTER() is a widely used DAX function and the functionality of this function is quiet intuitive from the function’s name. The function filters down the data into a subset of data which fulfills certain requirement or a set of requirements. The function works on the mechanism of virtual lineage. This means that a virtual table in Power BI’s vertipaq engine is created which has an active connection with the actual table and hence any change in the actual data effects the results of FILTER(). It is also used as virtual table in calculations. This blog presents a brief introduction to the FILTER() function in DAX with a working example.

Why not sign up for Power BI training in Australia. We provide our services in the following regions:
• Power BI Training in Sydney
• Power BI Training in Melbourne
• Power BI Training in Brisbane