Please note javascript is required for full website functionality.
MVP

Blog

Power Pivot Principles: How to Combine Three or More Filter Criteria into One CALCULATE Measure

11 December 2018

Welcome back to our Power Pivot blog.  Today, we dive a little deeper into the IN function.

 

Last week we looked at concatenating filters to combine multiple criteria into one CALCULATE function.  This week, we will improve on that concept using the IN function.

As a recap we used the following measure to illustrate combining two filter criteria into one measure. 

This measure achieves our desired result; however, it is a little complicated.  Additionally, what will we do if we have a third criteria that we want to add to the filter?  Let’s see if we can do better using the IN function. 

After using the IN function, we have achieved the same result in lesser steps and improved readability!

(Sorry about the fact all of our data has the same values per year – it is working, we assure you!)

Not only that but we can have more than two criteria in the filter.  Consider the following:

This results in: 

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.

Newsletter