COLLAPSE

In a visual calculation, an expression evaluated a level up an axis of the visual, or at the level of the columns named.

A visual calculation works on the table behind a visual: a row for each group on the visual's axes and one for each of their totals. To try one in the OAX view, give a table that shape with DEFINE TABLE ... WITH VISUAL SHAPE: its axes, their groups, the TRUE/FALSE column that marks each group's totals and the order, and with DENSIFY a name for the column that marks rows the shape fills in. Then write the calculation as a COLUMN of that table, as Power BI's own queries do. The examples below are queries of that kind, small enough to check by hand.

Group: Window and visual calculations. Compatible with DAX.

Syntax

COLLAPSE ( <expression>, <axis> [, <N>] )
COLLAPSE ( <expression>, <column> [, <column>] … )
COLLAPSE ( <axis> [, <N>] )
COLLAPSE ( <column> [, <column>] … )

Returns

Same type as the expression. The expression's value at the parent level of the current row.

Remarks

• On a quarter's row, COLLAPSE ( [Liters], ROWS ) is its year's liters; on a year's subtotal, the grand total. At the top level there is nothing higher, so the row's own value comes back.
• DIVIDE ( [Liters], COLLAPSE ( [Liters], ROWS ) ) is the classic share of the parent.
• With a column, the context goes up to the level just above that column's: COLLAPSE ( [Liters], [Quarter] ) gives the year on a quarter's row and leaves a year's row as it is.
• N must be 1 or more; more levels than there are stop at the top.
• With an expression, give an axis or a column: COLLAPSE ( [Liters] ) alone is an error.
• The forms without an expression are filters of CALCULATE: CALCULATE ( SUM ( [Liters] ), COLLAPSE ( ROWS ) ) is the same as COLLAPSE ( [Liters], ROWS ).
• The visual's table must hold the level's rows: in a shape without totals, there is no row a level up and the result is BLANK.
• It works only in a visual calculation; in a measure, a calculated column or a plain query it is an error.

Examples

Share of the parent

Years and quarters on the rows, with their subtotals and a grand total. Each quarter's share of its year, each year's share of all.

DEFINE
    TABLE Visual =
        DATATABLE (
            "Year", INTEGER, "Quarter", INTEGER, "Liters", INTEGER, "All years", BOOLEAN, "All quarters", BOOLEAN,
            {
                { 2025, 1, 100, FALSE, FALSE }, { 2025, 2, 120, FALSE, FALSE }, { 2025, BLANK (), 220, FALSE, TRUE },
                { 2026, 1, 130, FALSE, FALSE }, { 2026, 2, 150, FALSE, FALSE }, { 2026, BLANK (), 280, FALSE, TRUE },
                { BLANK (), BLANK (), 500, TRUE, TRUE }
            }
        )
        WITH VISUAL SHAPE
            AXIS ROWS
                GROUP [Year] TOTAL [All years]
                GROUP [Quarter] TOTAL [All quarters]
                ORDER BY [Year], [Quarter]
            DENSIFY "Added"
    COLUMN Visual[Parent] = COLLAPSE ( [Liters], ROWS )
    COLUMN Visual[Share] = DIVIDE ( [Liters], COLLAPSE ( [Liters], ROWS ) )
EVALUATE
SELECTCOLUMNS (
    Visual,
    "Year", [Year], "Quarter", [Quarter], "Liters", [Liters],
    "Parent", [Parent],
    "Share", [Share]
)
ORDER BY ISBLANK ( [Year] ), [Year], ISBLANK ( [Quarter] ), [Quarter]

Two levels up, or by a column

Two levels up from a quarter is the grand total. Collapsing by Quarter stops at the year.

DEFINE
    TABLE Visual =
        DATATABLE (
            "Year", INTEGER, "Quarter", INTEGER, "Liters", INTEGER, "All years", BOOLEAN, "All quarters", BOOLEAN,
            {
                { 2025, 1, 100, FALSE, FALSE }, { 2025, 2, 120, FALSE, FALSE }, { 2025, BLANK (), 220, FALSE, TRUE },
                { 2026, 1, 130, FALSE, FALSE }, { 2026, 2, 150, FALSE, FALSE }, { 2026, BLANK (), 280, FALSE, TRUE },
                { BLANK (), BLANK (), 500, TRUE, TRUE }
            }
        )
        WITH VISUAL SHAPE
            AXIS ROWS
                GROUP [Year] TOTAL [All years]
                GROUP [Quarter] TOTAL [All quarters]
                ORDER BY [Year], [Quarter]
            DENSIFY "Added"
    COLUMN Visual[Two up] = COLLAPSE ( [Liters], ROWS, 2 )
    COLUMN Visual[Without quarters] = COLLAPSE ( [Liters], [Quarter] )
EVALUATE
SELECTCOLUMNS (
    Visual,
    "Year", [Year], "Quarter", [Quarter], "Liters", [Liters],
    "Two up", [Two up],
    "Without quarters", [Without quarters]
)
ORDER BY ISBLANK ( [Year] ), [Year], ISBLANK ( [Quarter] ), [Quarter]

As a filter of CALCULATE

DEFINE
    TABLE Visual =
        DATATABLE (
            "Year", INTEGER, "Quarter", INTEGER, "Liters", INTEGER, "All years", BOOLEAN, "All quarters", BOOLEAN,
            {
                { 2025, 1, 100, FALSE, FALSE }, { 2025, 2, 120, FALSE, FALSE }, { 2025, BLANK (), 220, FALSE, TRUE },
                { 2026, 1, 130, FALSE, FALSE }, { 2026, 2, 150, FALSE, FALSE }, { 2026, BLANK (), 280, FALSE, TRUE },
                { BLANK (), BLANK (), 500, TRUE, TRUE }
            }
        )
        WITH VISUAL SHAPE
            AXIS ROWS
                GROUP [Year] TOTAL [All years]
                GROUP [Quarter] TOTAL [All quarters]
                ORDER BY [Year], [Quarter]
            DENSIFY "Added"
    COLUMN Visual[Parent] = CALCULATE ( SUM ( [Liters] ), COLLAPSE ( ROWS ) )
EVALUATE
SELECTCOLUMNS (
    Visual,
    "Year", [Year], "Quarter", [Quarter], "Liters", [Liters],
    "Parent", [Parent]
)
ORDER BY ISBLANK ( [Year] ), [Year], ISBLANK ( [Quarter] ), [Quarter]

Related functions

COLLAPSEALL, EXPAND, ISATLEVEL, LOOKUPWITHTOTALS, DIVIDE.

Last updated 3 November 2026 · OpenBI Studio 0.3.74