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.
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:
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.
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:
2 thoughts on “FILTER Function in DAX and Power BI: Apply Custom Filter to Calculations”
You are Amazing, Thank you so much
It is very useful to me at this moment