EXPAND

In a visual calculation, an expression evaluated a level down 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

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

Parameter	Type	Description
`expression`	Expression	What is worked out one or more levels down; it sees all the child rows, so it usually aggregates them (`AVERAGE ( [Liters] )`). Left out, EXPAND is a filter of `CALCULATE`.
`axis`	Axis	The axis to move down: `ROWS`, `COLUMNS`, or several of them.
`N`	Whole number optional	How many levels down; 1 when left out.
`column`	Column repeatable	Instead of an axis: fields of the visual. The context moves down to the level where they are grouped.

Returns

Same type as the expression. The expression's value over the current row's children.

Remarks

On a year's subtotal, EXPAND ( AVERAGE ( [Liters] ), ROWS ) averages that year's quarters; on the grand total, the years.
A field alone, [Liters], over several child rows is an error: wrap it in an aggregation such as SUM, AVERAGE or MAX.
At the bottom level there is nothing below, and the row itself is used.
N must be 1 or more. EXPANDALL goes straight to the bottom.
With an expression, give an axis or a column: EXPAND ( AVERAGE ( [Liters] ) ) alone is an error (example 3).
It works only in a visual calculation; in a measure, a calculated column or a plain query it is an error.

Examples

Average of the children

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[Average of children] = EXPAND ( AVERAGE ( [Liters] ), ROWS )
EVALUATE
SELECTCOLUMNS (
    Visual,
    "Year", [Year], "Quarter", [Quarter], "Liters", [Liters],
    "Average of children", [Average of children]
)
ORDER BY ISBLANK ( [Year] ), [Year], ISBLANK ( [Quarter] ), [Quarter]

Year	Quarter	Liters	Average of children
2025	1	100	100
2025	2	120	120
2025	(blank)	220	110
2026	1	130	130
2026	2	150	150
2026	(blank)	280	140
(blank)	(blank)	500	250

Down to a column

The best quarter under each row: the grand total looks at all four quarters, a year at its two.

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[Best quarter] = EXPAND ( MAX ( [Liters] ), [Quarter] )
EVALUATE
SELECTCOLUMNS (
    Visual,
    "Year", [Year], "Quarter", [Quarter], "Liters", [Liters],
    "Best quarter", [Best quarter]
)
ORDER BY ISBLANK ( [Year] ), [Year], ISBLANK ( [Quarter] ), [Quarter]

Year	Quarter	Liters	Best quarter
2025	1	100	100
2025	2	120	120
2025	(blank)	220	120
2026	1	130	130
2026	2	150	150
2026	(blank)	280	150
(blank)	(blank)	500	150

Without an axis

Nothing says which way to go down.

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[No axis] = EXPAND ( AVERAGE ( [Liters] ) )
EVALUATE
SELECTCOLUMNS (
    Visual,
    "Year", [Year], "Quarter", [Quarter], "Liters", [Liters],
    "No axis", [No axis]
)
ORDER BY ISBLANK ( [Year] ), [Year], ISBLANK ( [Quarter] ), [Quarter]

Result: an error.

EXPANDALL, COLLAPSE, ISATLEVEL, AVERAGE.

Last updated 3 November 2026 · OpenBI Studio 0.3.74