DAX SUMMARIZECOLUMNS function - How is this function useful?

Question

According to the DAX Guide (https://dax.guide/summarizecolumns/):

SUMMARIZECOLUMNS does not support evaluation within a context transition. This makes it not useful in most of the measures

This function is quite powerful and a nice successor to the SUMMARIZE function. But, I'm a bit curious as to why the DAX team would introduce a function like this when it can only be used in very limited scenarios. I guess if you're wanting to build a calculated table it would be useful. Other than that I think you're pretty much forced to use SUMMARIZE. Am I missing something?

Answer 1

According to Marco Russo, this limitation has been removed and SUMMARIZECOLUMNS can be effectively used within measures now in addition to static calculated tables.

There are still a couple of things you need to be aware of if you are looking to switch from SUMMARIZE to SUMMARIZECOLUMNS but Marco covers them in this article

---

Edit: After some testing, it appears I can use it in a measure that is responsive to external filter context (e.g. slicers or page filters) but won't work in the internal filter context produced by a visual (e.g. row/column in a matrix), so the limitation is only partly removed.

Answer 2

SUMMARIZECOLUMNS is hugely useful for queries. I am quite certain it was introduced by request of the Power BI team. Almost every visual in PBI generates a query where the bulk of the work is done in a SUMMARIZECOLUMNS. These are in the general form of:

DEFINE
VAR <filter1> = ...
VAR <filter2> = ...
...
VAR <filterN> = ...
EVALUATE
SUMMARIZECOLUMNS
    <grouping columns from visual>,
    <filter1>,
    <filter2>,
    ...,
    <filterN>,
    <measures in visual>

In this pattern, all crossfilters from other visuals and all filters in the report are captured in filter1, filter2, ..., filterN. This is a very easy pattern to generate programmatically.

It's also really useful for writing queries in general, as a single function gets you grouping, selection/filtering, and measure evaluation.

Sometimes it's easy to get blinders on, thinking of DAX as a formula language for measures only, but it is a fully fledged relational query language.