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

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 FILTERS. 

The FILTERS function

The FILTERS function returns the values that are directly applied as filters for an existing column, and it is quite similar to the DISTINCT function.  The function has the following syntax:

• columnName: this argument requires the name of an existing column using standard DAX syntax, and it cannot be an expression.

Here are a few remarks about this function:

• the FILTER function is usually used in other functions that require a column as an argument
• this function is quite useful when combined with the COUNTROWS function to count the number of filters that will be applied to a column
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Besides working as a column argument for other functions, we can also view the outputs from FILTERS in a table.  Consider the following example where we have a FinancialsTable Table with sales data from different countries:

After adding the Table to Data Model, we can write the following code in the ‘Edit DAX’ section to obtain a list of filters that are directly applied to the column Country:

The result is a Table with the filter values that are applied to the column Country:

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.