Filter SUMMARIZECOLUMNS

Question

How to construct filter tables for SUMMARIZECOLUMNS function?

The SUMMARIZECOLUMNS has the following pattern:

SUMMARIZECOLUMNS( 
    ColumnName1, ...
    ColumnNameN,
    FilterTable1,     -- my question concerns this line
    FilterTableN, 
    Name1, [measure1], 
    NameN, [measure2], 
)

I have checked that the following 3 patterns work. They return the same results, at least for the simple sample data I used.

SUMMARIZECOLUMNS (
    T[col],
    FILTER( T, T[col] = "red" ) 
)

SUMMARIZECOLUMNS (
    T[col],
    CALCULATETABLE( T, T[col] = "red" ) 
)

SUMMARIZECOLUMNS (
    T[col],
    CALCULATETABLE ( T, KEEPFILTERS ( T[col] = "red" ) )
)

Is any of these patterns superior over the other?

Reference: https://www.sqlbi.com/articles/introducing-summarizecolumns/

Update

I would be interested in an answer that contains a query plan analysis or link to credible source. I would be grateful if you mentioned using the SUMMARIZECOLUMNS function when grouping columns from multiple tables.

In general, I don't recommend adding bounty offers or negotiations into posts themselves, either for the time when a bounty is live, or in order to tempt new answers. This material can go into the bounty reason while a bounty is live, and is then removed automatically once the bounty expires; adding it to the question itself rather clutters up the problem with meta-commentary, which is not interesting to the broad audience (they do not have an account or sign in). — halfer, Feb 09 '20 at 20:23
@halfer regular bounty work very good with tags that have very broad audience. Tags such as `dax` or `powerbi` are not popular. There is narrow community of just a few people. Starting regular bounty does not even increase the views because those few people will see those few posts that are posted here every week. That is why I would like to start a bounty that may encourage people to tackle the problem. The question itself is not a bug correction request. There is a possibility ot reward existing answer and I would like to inform community that I am going to do it if answer appears. Why not? — Przemyslaw Remin, Feb 10 '20 at 08:54
Thank you Przemyslaw. I am working on the "first principle" that questions should contain question material only, and answers should contain answer material only. The Meta community and mods are generally in agreement with this - we keep material free from casual conversation, what time of day it is, greetings and Christmas wishes, hopes that the reader is well, etc. In the same way, commentary about voting is ephemeral and distracting. — halfer, Feb 10 '20 at 08:57
Since there are two other good ways to note bounty information (comments, bounty notice) I think they are preferable. I might see if I can find the time to ask _Meta_ a question about it. — halfer, Feb 10 '20 at 08:57
(To a lesser degree I am conscious that bounties are used as a device to obtain free work - the rules for obtaining a bounty become highly detailed work orders and the OP feels that they no longer have to be an active participant in the work. I think perhaps we could discourage that too - they are after all just unicorn points, and helpers should be free to answer how they wish, to some extent). — halfer, Feb 10 '20 at 09:01
@halfer I would gladly continue this discussion on chat, but it seems I have to wait for that option until we exchange more comments. The discussion is if there should be an option for starting "life-long" bounties which will be granted when answer solves the problem stated in question. I say YES, because many difficult question require more than a week to answer. Here is list of such problems, maybe not related by topic to SO but analogy works here. https://en.wikipedia.org/wiki/Lists_of_unsolved_problems — Przemyslaw Remin, Feb 10 '20 at 13:20
For a "life-long bounty", I think it is not too verbose to add the requirements to the question, as per my edit. I recommend that there is no mention of explicit bounties, nor long lists of exceptions where a bounty won't be paid out. That is the material I think is not succinct enough for the question, and as I say, it is not of interest to most readers. — halfer, Feb 10 '20 at 13:24
(Indeed once a bounty is paid out, all of that would have to be removed anyway, on the basis that the requirements are satisfied. My experience is that sort of tidying and curation is nearly never done on Stack Overflow, and the solution is to ensure it does not go into the Q in the first place). — halfer, Feb 10 '20 at 13:25

score 6 · Accepted Answer · answered Feb 03 '20 at 20:20

You can also construct them the way PowerBI does, using VAR:

VAR  __MyFilterTable = FILTER( T, T[col] = "red" ) 

RETURN
SUMMARIZECOLUMNS (
    T[col],
    __MyFilterTable
)

Which is more efficient will depend on the complexity your filtering, so there is no "one size fits all" rule necessarily. For a simple table level filter, just FILTER will suffice. I caution you that Line 1, where you're filtering the entire table T, is a bad idea. It's much more performant to only filter a single column. When you filter the entire table, DAX materializes the entire table in memory, while the following just materializes the one value of T[col]:

VAR  __MyFilterTable = FILTER( ALL(T[col]), T[col] = "red" ) // This is better.

RETURN
SUMMARIZECOLUMNS (
    T[col],
    __MyFilterTable
)

You can do even better than that, conceptually. You can basically tell DAX, "I know this is a value, so don't even look in the table for it. Just make me a table and treat it as though I filtered it. Like this:

VAR  __MyFilterTable = TREATAS ({"red"}, T[col] )

RETURN
SUMMARIZECOLUMNS (
    T[col],
    __MyFilterTable
)

Again, this is the pattern that PowerBI uses when performing its filters.

BTW, Creating the filter tables a the top vs. creating them inline with SUMMARIZECOLUMNS() won't make any difference for speed. Avoid using CALCULATETABLE() as you've done here generally.

You can also do this as well, though you aren't likely to see a speed increase generally:

CALCULATETABLE(
    SUMMARIZECOLUMNS (
        T[col]
    ),
    KEEPFILTERS(T[col] = "red")
)

Thank you. How have you learned that "this is better", and the other one "even better"? Can you please point to any source? — Przemyslaw Remin, Feb 04 '20 at 09:27
There is just not a lot of definitive documentation out there. Of course, Marco Russo's/Alberto Ferrari's excellent "The Definitive Guide to DAX, Second Edition" (throw away your first edition and buy the new one!) is a great reference. But the reason I KNOW is the "Server Timings" window in DAX Studio, which reiterates the concepts in Marco's book. You can see for yourself the size in rows and size of bytes of each Storage Engine query (if you don't know what a SE Query is, refer to the book above) Also https://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf — Dave Markle, Feb 04 '20 at 12:03
The last code you pointed to, wrapping Summerizecolumns with CalculateTable, has a possible tremendous advantage. Say we want to filter BOTH dim1[Color]= 'red' and dim2[Size]='big'. It is easy to trap such a multiple dimension condition with an outside filter. I wonder if it might be possible with the inner filter inside Summerizecolumns. — Przemyslaw Remin, Mar 04 '21 at 08:59

Filter SUMMARIZECOLUMNS

Update

1 Answers1

Linked