none
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?

    Thursday, September 08, 2011 4:28 AM

Answers

All replies

  • 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.


    Thursday, September 08, 2011 7:35 AM
    Moderator
  • 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.

    Friday, September 09, 2011 5:30 AM
    Moderator
  • 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
    • Marked as answer by ruve1k Sunday, September 11, 2011 9:26 PM
    Friday, September 09, 2011 1:50 PM