Please note javascript is required for full website functionality.
MVP

Blog

Power Pivot Principles: KEEPFILTERS to the Rescue

15 January 2019

Welcome back to our Power Pivot blog.  Today, we discuss an alternate way to keep the CALCULATE function from misbehaving.

 

Last week, we looked at using the CALCULATE, FILTER and VALUES functions to stop measures from overriding the row context values (you can read about it here).

Here is how we used the three functions last week: 

There is an alternative way to avoid the measure from overriding row context values.  This can be achieved by employing the KEEPFILTERS function.  We can use the KEEPFILTERS function directly after the CALCULATE function, as in this following example:

=CALCULATE(

                [Sales],

                KEEPFILTERS( 'Product SubCategory'[ProductCategoryKey] = 2 )

                )

As you can see (in the picture below) we have achieved the same result in fewer steps, which is usually good when coding!

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