SUMMARIZECOLUMNS

Groups by columns of any tables, with filters and measures; rows where every measure is blank left out.

Group: Table manipulation. Compatible with DAX.

Syntax

SUMMARIZECOLUMNS ( [<groupBy_columnName> [, <groupBy_columnName>] …]
    [, <filterTable>] … [, <name>, <expression>] … )

Parameter	Type	Description
`groupBy_columnName`	Column optional, repeatable	A column to group by, from any table of the model.
`filterTable`	Table optional, repeatable	A filter for the whole calculation, such as `FILTER` or `TREATAS` over a column.
`name`	Text optional, repeatable	The name of a column of values, in quotes.
`expression`	Expression optional, repeatable	Its value for each group, usually a measure.

Returns

Table. One row for each combination of the grouping columns' values that the filters leave, with a column per expression.

Remarks

A row where every expression is blank is left out. Adding 0 to a measure ([Liters sold] + 0) brings every such row back with a zero, for each combination of the grouping columns: use it with care.
Columns of one table combine as that table holds them. Columns of different tables combine through the expressions: a pair stays when an expression has a value for it. With no expression at all, they combine in every way, as with CROSSJOIN.
Filter arguments filter both the groups and the expressions.
IGNORE keeps an expression out of the blank test, ROLLUPADDISSUBTOTAL adds subtotal rows, and NONVISUAL marks a filter that ALLSELECTED should leave out.
It is the function behind the queries of Power BI's visuals, and the usual way to group in a query.

Compared with DAX

OAX also takes a TRUE/FALSE condition on one table's columns as a filter argument, such as Products[Product] = "Pils", and reads it as CALCULATE does: FILTER ( ALL ( Products[Product] ), Products[Product] = "Pils" ). Power BI Desktop refuses such a condition in SUMMARIZECOLUMNS; write the FILTER form in a query that must run in both. On a live connection, OpenBI sends the server the FILTER form.

Examples

Liters and revenue by product

EVALUATE
SUMMARIZECOLUMNS (
    Products[Product],
    "Liters", [Liters sold],
    "Revenue", [Revenue]
)
ORDER BY Products[Product]

Products[Product]	Liters	Revenue
Cider	301074	15655848
IPA	331884	21240576
Lettøl	169179	5075370
Pils	1004326	38164388

Combinations without data are left out

Depots sell nothing, so no depot row appears.

EVALUATE
SUMMARIZECOLUMNS ( Products[Category], Places[Kind], "Liters", [Liters sold] )
ORDER BY Products[Category], Places[Kind]

Products[Category]	Places[Kind]	Liters
Beer	Shop	1505389
Cider	Shop	301074

A filter argument

EVALUATE
SUMMARIZECOLUMNS (
    Products[Product],
    FILTER ( ALL ( Products[Category] ), Products[Category] = "Beer" ),
    "Liters", [Liters sold]
)
ORDER BY Products[Product]

Products[Product]	Liters
IPA	331884
Lettøl	169179
Pils	1004326

SUMMARIZE, ADDCOLUMNS, IGNORE, ROLLUPADDISSUBTOTAL, NONVISUAL.

Last updated 3 November 2026 · OpenBI Studio 0.3.74