What does the KEEPFILTERS function do?

Question

I can't find any references to the KEEPFILTERS function other than the insert function dialog box (and intellisense auto-complete.) 

The insert function dialog box has the following terse description:

KEEPFILTERS(Expression)

Changes the CALCULATE and CALCULATETABLE function filtering semantics

Can anyone elaborate on that, or can anyone describe how to actually use it?

Answer 1

Hi ruve1k,

It seem this function is not documented on MSDN book on line, you can also refer to this link http://msdn.microsoft.com/en-us/library/ee634396(v=SQL.110).aspx.

It is just a function, if you have any requirement, please feel free to post your question.

Thanks,
Challen Fu

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hi ruve1k,

After consulting with PowerPivot talk group, I get that:

Before KeepFilters, a new filter added to filter context will overwrite existing filters that have the same columns. Using KeepFilters, you can keep any previous filters so both the new one and the old one take effect.

For example,  

CalculateTable(CalculateTable(T, T[C]=1 || T[C]=2), T[C]=2 || T[C]=3) returns rows from T where [C]=1 or [C]=2, only the latest (innermost) filter takes effect.

CalculateTable(CalculateTable(T, KeepFilters(T[C]=1 || T[C]=2)), T[C]=2 || T[C]=3) returns rows from T where [C] = 2, both filters take effect (you can think of it as the intersection of both filters).

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

Challen,

I spent some time to write a blog post about KEEPFILTERS. A really interesting function, thanks to ruve1k who has pointed our interest on this, I learned a lot while studying the new semantics of CALCULATE introduced by KEEPFILTERS.

Here's the post: http://sqlblog.com/blogs/alberto_ferrari/archive/2011/09/09/keepfilters-a-new-dax-feature-to-correctly-compute-over-arbitrary-shaped-sets.aspx

HTH

---

Alberto Ferrari
http://www.powerpivotworkshop.com