What does the KEEPFILTERS function do? RRS feed

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


    Changes the CALCULATE and CALCULATETABLE function filtering semantics

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

    Thursday, September 8, 2011 4:28 AM


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.

    Challen Fu

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

    • Edited by Challen Fu Friday, September 9, 2011 5:28 AM
    Thursday, September 8, 2011 7:35 AM
  • 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 9, 2011 5:30 AM
  • 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


    Alberto Ferrari
    • Marked as answer by ruve1k Sunday, September 11, 2011 9:26 PM
    Friday, September 9, 2011 1:50 PM