- SUMMARIZECOLUMNS is
the primary querying function used by Power BI.
And the reason is in SUMMARIZECOLUMNS,
with a single function call,
you can specify most of the arguments of a typical query.
You provide to SUMMARIZECOLUMNS three kind of arguments,
which are the groupby columns,
the filters and the column
that you want to create in the output.
Let's see that with an example.
In this first example, I'm using SUMMARIZECOLUMNS
and I'm specifying as groupby columns
the product brand and the calendar year.
So SUMMARIZECOLUMNS will generate all the combination
of brand and calendar year.
Before doing that, it needs to apply the filters,
which are specified in the next argument.
SUMMARIZECOLUMNS detects that an argument
is a filter because it is a table.
And in this case, I'm using TREATAS
to retrieve 2008 and 2009 as calendar year.
Therefore, this table filters the date table
for only the years which are 2008 and 2009.
Same is happening here with the second filter
that is filtering the product table
where the color is either red or blue.
So once the filter has been applied, SUMMARIZECOLUMNS
generates the combinations of the groupby column
and it finally computes the new column.
In this case, they are amount and quantity.
For each new column, I need to provide the column name
and the expression that computes the column value.
These expressions are evaluated in a filter context
that contains the currently selected value...
which is being iterated by SUMMARIZECOLUMNS.
So SUMMARIZECOLUMNS, unlike SUMMARIZE,
relies only on the filter context.
It generates no row context,
and this makes it much easier to use
because you don't have to worry about the interaction
between the row context and the filter context
that create issues in SUMMARIZE.
SUMMARIZECOLUMNS can also create...
new rows for the total, both new rows and new columns.
If you want subtotals, you can specify that
with one modifier that is ROLLUPADDISSUBTOTAL.
In this case, I'm using ROLLUPADDISSUBTOTAL
and I say that I want not only to group by brand
but I also want the subtotal at the brand level.
And I also need to specify a new name.
This is the name of a new column
that contains a Boolean value, either true or false.
It is true if the given row contains
a subtotal at the given level.
Because I'm requesting the subtotal,
both for the brand and for the year,
you see that in the result, I have additional rows.
This one, for example, shows a blank for the brand,
a value for the year, meaning that
this is the subtotal at the brand level.
And you can also tell it because
the brand total column contains true.
The same happens for the subtotals at the year level
that contains true for the year total.
I have a value for the brand and no value for the year.
And finally, I have the grand total
that contains true for all the total columns
and no value for either the year and the brand.
And this is the grand total.
It is useful whenever you want
to express in one single query not only the values
of individual cells but also the subtotals.
And SUMMARIZECOLUMNS is able to compute that.
An important note about SUMMARIZECOLUMNS
is that rows that contain blank for all the measures
are not returned as part of the result.
In this case, I'm summarizing by calendar year
and I'm computing the sales amount.
Even though my data model contains years,
which happen to be before 2007
and also years which are after 2009,
these values are not returned
because sales amount there would return a blank.
You can change this behavior using the IGNORE modifier.
An important note about SUMMARIZECOLUMNS,
even though it's rather technical,
is the behavior of auto exist.
If you specify multiple filters as part of SUMMARIZECOLUMNS
and these filters operate on the same table,
they are merged together before being applied.
This behavior is known as auto exist
and in some borderline scenarios
might produce really hard to understand values.
If you are interested in more details,
you can go reading this article that explains
in greater detail all the details of auto exist.
In this example, I'm using two filters on the date,
one on the calendar year and one on the month.
Even though the two filters are separate filters,
they're actually joined together.
So the two filters are merged
and they represent only one filter on the date table.
Multiple filters specified in SUMMARIZECOLUMNS,
if they operate on the same table,
they are merged together because of auto exist.
Finally, SUMMARIZECOLUMNS has a strong limitation
that you need to be aware of.
SUMMARIZECOLUMNS cannot be executed
if there is a row context or a filter context
that was generated by context transition
or an arbitrarily-shaped set in the filter context.
This limitation is much harder to express
than to actually obey because it basically means
that you cannot use SUMMARIZECOLUMNS
as part of a measure definition.
Whatever measure you will author in Power BI, for example,
that measure will be executed inside either a row context
or a filter context generated by context transition,
due to the way the client tool generates the query that
produces the number that you are interested in looking at.
Therefore, you can use SUMMARIZECOLUMNS
in a query but you basically cannot use
SUMMARIZECOLUMNS as part of a measure.
There, you will need to rely on SUMMARIZE, ADDCOLUMNS,
and simpler functions that can be executed
in any kind of filter context.
If you look at this example,
I'm calling SUMMARIZECOLUMNS inside an iteration
over the values of calendar year.
Therefore, there is a row context
and all what I obtain is an error
that says that SUMMARIZECOLUMNS and ADDMISSINGITEMS add missing items
cannot be used in this specific context.