RANGE

In a visual calculation, the rows within a number of steps of the current one along an axis.

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

RANGE ( <step> [, <includeCurrent>] [, <axis>] [, <orderBy>] [, <blanks>] [, <reset>] )

Parameter	Type	Description
`step`	Whole number	Negative: that many rows before the current one; positive: that many after; 0: the current row alone.
`includeCurrent`	TRUE/FALSE optional	TRUE (when left out) to count the current row in; FALSE to leave it out.
`axis`	Axis optional	Which way to go: `ROWS` or `COLUMNS`, or both, as `ROWS COLUMNS`, which goes down the rows first and then across the columns; `ROWPAGES` for small multiples. Left out, the visual's first axis.
`orderBy`	ORDERBY optional	`ORDERBY ( ... )` to take the rows in another order than the axis has.
`blanks`	Keyword optional	With an `ORDERBY`: where blank values go, `DEFAULT`, `FIRST` or `LAST`.
`reset`	Reset optional	Where it starts again: `NONE` (when left out) never; `HIGHESTPARENT` (or 1, or the axis's top column) at each value of the top group; `LOWESTPARENT` (or -1) at each row's own parent; another whole number, or a column of the axis, at that level.

Returns

Table. The rows of the visual in the window, to iterate over or to filter by.

Remarks

SUMX ( RANGE ( -1 ), [Liters] ) adds the row before and the current one; RANGE ( -1, FALSE ) keeps the row before only.
At the edges the window is cut short: the first row has nothing before it.
As a filter, CALCULATE ( SUM ( [Liters] ), RANGE ( -1 ) ) gives the same answer as the SUMX form.
MOVINGAVERAGE is the usual shortcut for the mean over such a window.
It works only in a visual calculation; in a measure, a calculated column or a plain query it is an error.

Examples

Sums over windows

DEFINE
    TABLE Visual =
        DATATABLE (
            "Month", INTEGER, "Liters", INTEGER,
            { { 1, 100 }, { 2, 150 }, { 3, 120 }, { 4, 90 } }
        )
        WITH VISUAL SHAPE
            AXIS ROWS GROUP [Month] ORDER BY [Month]
            DENSIFY "Added"
    COLUMN Visual[This and before] = SUMX ( RANGE ( -1 ), [Liters] )
    COLUMN Visual[Before only] = SUMX ( RANGE ( -1, FALSE ), [Liters] )
    COLUMN Visual[This and next] = SUMX ( RANGE ( 1 ), [Liters] )
EVALUATE
SELECTCOLUMNS (
    Visual,
    "Month", [Month], "Liters", [Liters],
    "This and before", [This and before],
    "Before only", [Before only],
    "This and next", [This and next]
)
ORDER BY [Month]

Month	Liters	This and before	Before only	This and next
1	100	100	(blank)	250
2	150	250	100	270
3	120	270	150	210
4	90	210	120	90

Counting the rows, and as a filter

DEFINE
    TABLE Visual =
        DATATABLE (
            "Month", INTEGER, "Liters", INTEGER,
            { { 1, 100 }, { 2, 150 }, { 3, 120 }, { 4, 90 } }
        )
        WITH VISUAL SHAPE
            AXIS ROWS GROUP [Month] ORDER BY [Month]
            DENSIFY "Added"
    COLUMN Visual[Rows] = COUNTROWS ( RANGE ( -2 ) )
    COLUMN Visual[Filtered] = CALCULATE ( SUM ( [Liters] ), RANGE ( -1 ) )
EVALUATE
SELECTCOLUMNS (
    Visual,
    "Month", [Month], "Liters", [Liters],
    "Rows", [Rows],
    "Filtered", [Filtered]
)
ORDER BY [Month]

Month	Liters	Rows	Filtered
1	100	1	100
2	150	2	250
3	120	3	270
4	90	3	210

MOVINGAVERAGE, WINDOW, SUMX, AVERAGEX.

Last updated 3 November 2026 · OpenBI Studio 0.3.74