Power Pivot Principles: Introducing the FILTERS Function
21 July 2020
Welcome back to the Power Pivot Principles blog. This week, we will talk about the FILTERS function in DAX.
The FILTERS function returns the values that are directly applied as filters to the columnName. This function has the following syntax:
FILTERS(columnName)
For example, consider that we have Sales data already loaded into the Power Pivot Data Model:
We want to know the number of direct filters on the columns ‘Store Key’, ‘Product’ and ‘Customer Key’ have been applied to the context where the expression is being evaluated.
In Power Pivot, we create the following measures:
- ‘Filters Store Key’:
=COUNTROWS(FILTERS(Sales[Store Key]))
- ‘Filters Product’:
=COUNTROWS(FILTERS(Sales[Product]))
- ‘Filters Customer Key’:
=COUNTROWS(FILTERS(Sales[Customer Key]))
They will give us results as follows:
It may sound similar to the DISTINCT and the DISTINCTCOUNT functions we mentioned previously, so we’d want to compare between them, by creating measures using these functions. There is a slight difference between these two functions as we discussed here.
- ‘Distinct Store Key’:
=COUNTROWS(DISTINCT(Sales[Store Key]))
- ‘Distinct Product’:
=COUNTROWS(DISTINCT(Sales[Product]))
- ‘Distinctcount Customer Key’:
=DISTINCTCOUNT(Sales[Customer Key])
They just give us exactly the same results:
That’s it for this week!
Stay tuned 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.
