Power Pivot Principles: Concatenating Filters

Welcome back to our Power Pivot blog. Today, we look at using concatenation with the FILTER function to combine filter criteria.

Say we wanted to create a measure that calculates the total sales amount for April 2015 and May 2016 from our data set. How would we do that?

One simple solution would combine two CALCULATE functions together:

That would yield the following:

A more delicate solution would involve a combination of the CALCULATE, FILTER, ALL and OR functions to create the measure.

Note that we can concatenate two criteria into one by using the ‘&&’ syntax:

This allows us to combine the two criteria into one CALCULATE function.

The type of criteria that can be used in this manner is not limited to dates. We can also use Product Category Keys and Standard Costs criteria in the same manner to build a similar measure.

This measure yields:

That’s it for this week, happy pivoting!

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.