In this post we talk about the DAX Formula called Filter. Filter as its name suggests is used to filter the data in a table based on some condition. Filter is not used on its own but in combinations with some other Formula such as Calculate or SUMX etc. Filter DAX Formula is one of the most useful ones and you will find yourself using its quite often. For example to filter rows where the value is throwing and error, or filter products by some category etc.
The Syntax for Filter is shown below
It expects a table on which the filter needs to be applied, followed by the expression using a column within that table.
FILTER('Fact Sale','Fact Sale'[Quantity]<1002)
The above screenshot shows an example where a Filter is applied to the FACT Sale table to filter rows where the Quantity is less than 1002. You easily change this to greater than 100 to find out customers who have placed orders where they have purchased more than a 100 units of an item.
The SQL Equivalent of the above function would be similar to
SELECT * FROM Orders WHERE Qty < 1002