Power Pivot Principles: The A to Z of DAX Functions – FILTER

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at FILTER. 

The FILTER function

The FILTER function is one of the filter functions which returns a table that is filtered.  It has the following syntax:

• table: this argument requires a table or an expression that results in a table.  This table will be then filtered
• filter: this argument required a Boolean expression that is evaluated for each row of the table. 

Here are a few remarks about this function:

• the FILTER function is quite useful for those users whose wish to reduce the number of rows in the table they are working with.  This function usually uses in other functions that require a table as an argument
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Please consider the following example where we have a Customer table with 18 customers from different countries:

We can write the following code in the ‘Edit DAX’ section to filter all values of Customer Name where the Country is Germany:

The result of this code is a filtered table where Country is Germany:

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.