How To Filter Data Based On The Measure Greater Than Zero Using Dax Ssas

Filter office in DAX and Power BI; how information technology tin be used

Yous tin can utilise filtering to visualization elements. However, sometimes filtering has to exist washed on specific calculations. This can come in handy especially when you desire 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 oftentimes used to filter rows of a table. The Filter role keeps the columns untouched, and it simply reduces the number of rows based on filter criteria. The Filter function is a tabular function (it returns a table every bit the result). It can be used to create a calculated tabular array, or every bit a table input parameter for other functions. Hither is the syntax of using this office:

FILTER(<table>,<filter>)

As you can see the syntax is very simple, including merely ii parameters;

table: the tabular array which we want to be filtered.
filter: the status(s) of filtering. This is an expression with a Boolean result (ways has to return true or fake)

Samples of using Filter every bit a tabular array expression

Here are some samples of using the Filter function as a calculated tabular array. Let's start with a most bones sample. If nosotros desire to accept a subset of the DimProduct table for those products that their Color is Red, the expression can exist every bit below;

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

The Colour field in the DimProduct will be filtered to only include Ruby-red as below;

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

Note that DAX is not case-sensitive, "Cherry" and "ruby-red" would be the same. If you lot desire to brand it case-sensitive, you lot tin can utilize exact match functions every bit I explained hither.

Filter function with multiple conditions

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

                Filter 2nd example = FILTER(     DimProduct,     DimProduct[Color]='Carmine'      &&     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 atmospheric condition in Power BI

Filter does not alter columns

The Filter function only reduces the rows of a table. Information technology will non alter the number of columns or the gild of it. You can apply other functions such every bit the SELECTCOLUMNS or the ADDCOLUMNS or whatever other table manipulation functions to practise that.

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

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

Using filter part inside other functions in Power BI and DAX

Using Filter office in a measure

Similar to many other tabular functions, the mutual use example scenario for them is to use them within a mensurate. Using a tabular function within a measure allows us to create virtual tables dynamically based on the filter atmospheric condition in the visualizations in the table.

Equally an case, I can use the below lawmaking to go the Sales of all products that their colour 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 Ability BI

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

Summary

The FILTER function in DAX is a uncomplicated 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. Like to many other tabular functions, the main benefit of this function is when used to create a virtual tabular array in a measure expression.

Download Sample Ability BI File

Download the sample Power BI report hither:

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Manager, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more 20 years' feel in information analysis, BI, databases, programming, and development more often than not on Microsoft technologies. He is a Microsoft Data Platform MVP for ix continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an agile 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, specially on MS BI, can be establish on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was too an agile fellow member on online technical forums such every bit 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 grouping. He is also the author of very pop book Power BI from Rookie to Stone Star, which is free with more than than 1700 pages of content and the Ability BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Top, PASS Summit, SQL Sat and SQL user groups. And He is a Microsoft Certified Trainer.
Reza's passion is to assistance y'all find the best data solution, he is Data enthusiast.