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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
Filter function in DAX and Power BI; how it can be used

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;

Filter function in DAX used to filter a table with one condition in Power BI

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.

Using filter function with multiple conditions in Power BI

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 inside other functions in Power BI and DAX

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.

Filter function used in a DAX measure in Power BI

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:

    Enter Your Email to download the file (required)

    FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
    Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
    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 nine 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.
    His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
    He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
    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.

    Leave a Reply