MATCHBY
In a window function, the columns that identify the current row.
Group: Window and visual calculations. Compatible with DAX.
Syntax
MATCHBY ( <matchBy_columnName> [, <matchBy_columnName>] … )| Parameter | Type | Description |
|---|---|---|
matchBy_columnName | Column repeatable | A column of the relation that, with the others named and the PARTITIONBY columns, tells its rows apart. |
Returns
None. It gives no value of its own: it tells the window function around it how to find the current row.
Remarks
- Without MATCHBY, every column of the relation must match the context to find the current row. MATCHBY names the columns to match on instead.
- The MATCHBY columns together with the PARTITIONBY columns must tell every row of the relation apart; when two rows share them, it is an error.
- MATCHBY needs the relation: it can't be used when the relation is left out.
- It goes only inside a window function; anywhere else it is an error.
Examples
Months and shops identify a row
Within each shop, the month tells the rows apart: MATCHBY names it, PARTITIONBY adds the shop.
EVALUATE
VAR Pils =
FILTER (
Sales,
Sales[Product] = "Pils"
&& ( Sales[Shop] = "Shop Kampen" || Sales[Shop] = "Shop Sagene" )
&& YEAR ( Sales[Month] ) = 2026 && MONTH ( Sales[Month] ) <= 3
)
RETURN
ADDCOLUMNS (
Pils,
"Month before",
MAXX (
OFFSET (
-1,
Pils,
ORDERBY ( Sales[Month] ),
PARTITIONBY ( Sales[Shop] ),
MATCHBY ( Sales[Month] )
),
Sales[Liters]
)
)
ORDER BY Sales[Shop], Sales[Month]| Sales[Month] | Sales[Shop] | Sales[Product] | Sales[Liters] | Month before |
|---|---|---|---|---|
| 2026-01-01 | Shop Kampen | Pils | 278 | (blank) |
| 2026-02-01 | Shop Kampen | Pils | 326 | 278 |
| 2026-03-01 | Shop Kampen | Pils | 278 | 326 |
| 2026-01-01 | Shop Sagene | Pils | 260 | (blank) |
| 2026-02-01 | Shop Sagene | Pils | 275 | 260 |
| 2026-03-01 | Shop Sagene | Pils | 262 | 275 |
A month alone is not enough
Without the partition by shop, each month is there twice, once per shop.
EVALUATE
VAR Pils =
FILTER (
Sales,
Sales[Product] = "Pils"
&& ( Sales[Shop] = "Shop Kampen" || Sales[Shop] = "Shop Sagene" )
&& YEAR ( Sales[Month] ) = 2026 && MONTH ( Sales[Month] ) <= 3
)
RETURN
ADDCOLUMNS (
Pils,
"Month before",
MAXX ( OFFSET ( -1, Pils, ORDERBY ( Sales[Month] ), MATCHBY ( Sales[Month] ) ), Sales[Liters] )
)Result: an error.