FILTER Function in DAX and Power BI: Apply Custom Filter to Calculations

You can apply filtering to visualization elements. However, sometimes filtering has to be done on specific calculations. This can come in handy especially when you want to compare values of other items with specific items. In this article and video, I'll explain how to use the FILTER function in DAX to apply a custom filter in the calculations of Power BI.

Video

FILTER Function in DAX

The FILTER function often used to filter rows of a table. The Filter function keeps the columns untouched, and it just reduces the number of rows based on filter criteria. The Filter function is a tabular function (it returns a table as the result). It can be used to create a calculated table, or as a table input parameter for other functions. Here is the syntax of using this function:

FILTER(<table>,<filter>) 

As you can see the syntax is very simple, including just two parameters;

• table: the table which we want to be filtered.
• filter: the condition(s) of filtering. This is an expression with a Boolean result (means has to return true or false)

Samples of using Filter as a table expression

Here are some samples of using the Filter function as a calculated table. Let’s start with a most basic sample. If we want to have a subset of the DimProduct table for those products that their Color is Red, the expression can be as below;

Filter 1st example = FILTER(
    DimProduct,
    DimProduct[Color]='Red')

The Color field in the DimProduct will be filtered to only include Red as below;

Note that DAX is not case-sensitive, “Red” and “red” would be the same. If you want to make it case-sensitive, you can use exact match functions as I explained here.

Filter function with multiple conditions

Filter expression can have multiple conditions too. You can separate them using AND or OR functions, or their equivalent operators (&& and ||);

 Filter 2nd example = FILTER(
    DimProduct,
    DimProduct[Color]='Red' 
    &&
    DimProduct[SizeUnitMeasureCode]='CM')

The “&&” in the expression above means AND. The output will be only products with their color as red, and their SizeUniteMeasureCode as CM.

Filter does not change columns

The Filter function only reduces the rows of a table. It will not change the number of columns or the order of it. You can use other functions such as the SELECTCOLUMNS or the ADDCOLUMNS or any other table manipulation functions to do that.

Filter 3rd example = 
var filtered=
FILTER(
    DimProduct,
    DimProduct[Color]='Red' &&
    DimProduct[SizeUnitMeasureCode]='CM')
return
SELECTCOLUMNS(
    filtered,
    'Product Name',
    DimProduct[EnglishProductName]
)

In the expression above, the result of the FILTER function (which is a virtual table) is used as an input table parameter for the SELECTCOLUMNS function. As a result we have a table with one column: name of the products which their color is red and their size unit is CM.

Using Filter function in a measure

Similar to many other tabular functions, the common use case scenario for them is to use them inside a measure. Using a tabular function inside a measure allows us to create virtual tables dynamically based on the filter conditions in the visualizations in the table.

As an example, I can use the below code to get the Sales of all products that their color is red OR their size unit measure is CM.

Sales of Red OR CM = 
var filtered=
FILTER(
    DimProduct,
    DimProduct[Color]='Red' || DimProduct[SizeUnitMeasureCode]='CM'
)
return
CALCULATE(
    [Sales],
    filtered)

The “||” in the expression means OR.

The result of filtered expression is used as an input to the Calculate function to provide the sales of the filtered data.

Summary

The FILTER function in DAX is a simple function to use for filtering rows of a table. This function does not change the columns (unless it is used as an input of column manipulation functions such as SELECTCOLUMNS or ADDCOLUMNS). The filter function requires a table input and an expression. The expression should return true or false, and can include AND/OR functions or operators. Similar to many other tabular functions, the main benefit of this function is when used to create a virtual table in a measure expression.

Download Sample Power BI File

Download the sample Power BI report here:

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.