COLLAPSE DAX Function (Visual calculations)
Retrieves a context with removed detail levels compared to the current context. With an expression, returns its value in the new context, allowing navigation up hierarchies and calculation at a coarser level of detail.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| Expression | Optional |
The expression to be evaluated in the new context. |
| Axis | Optional |
An axis reference. |
| Column | Optional Repeatable |
A column in the data grid. |
| N | Optional |
The number of levels to collapse. If omitted, the default value is 1. |
Return values
The value of Expression after COLLAPSE moves the context up one or more levels. Without Expression, COLLAPSE does not return a value: it changes the context, and you can use it only as a filter argument of CALCULATE.
Notes
The visual calculation functions have an axis parameter that is defined in the VISUAL SHAPE query syntax produced by the visual calculation feature in Power BI.
Remarks
COLLAPSE can be used only in a visual calculation. In a measure or in a calculated column, COLLAPSE returns an error.
COLLAPSE has two forms. With Expression, COLLAPSE moves the context up the hierarchy and returns the value of Expression in the new context. With Expression, you must also use Axis or at least one Column, otherwise COLLAPSE returns an error. Without Expression, COLLAPSE only moves the context. You can use this form only as a filter argument of CALCULATE, for example CALCULATE ( [Sales Amount], COLLAPSE ( ROWS ) ).
With Axis, the levels are all the columns of that axis. N is the number of levels to move up, and the default is 1. N must be 1 or more, otherwise COLLAPSE returns an error. COLLAPSE rounds a decimal N to the nearest integer. If N is larger than the number of levels above the current cell, COLLAPSE stops at the top level (the total of the axis).
With one or more Column arguments, COLLAPSE moves up to the first level that does not include any of the columns. If the current cell does not include the columns, COLLAPSE does not move. For example, with Year, Quarter, and Month on rows, COLLAPSE ( [Sales Amount], [Month] ) returns the quarter value in a month row, and the value of the current cell in a quarter, year, or total row.
On the top level, COLLAPSE returns the value of the current cell. If the visual does not have the axis (for example, COLUMNS in a visual with only rows), COLLAPSE does not move.
To move directly to the top level of an axis, use COLLAPSEALL. To move down the hierarchy, use EXPAND.
Examples
-- COLLAPSE moves the visual calculation up the ROWS axis.
-- The query simulates a matrix with Year, Quarter, and Month on rows.
-- Parent: one level up (N = 1 is the default)
-- Grandparent: two levels up
-- QuarterOrSelf: first level without Month (no move on Quarter, Year, Total)
-- % of Parent: COLLAPSE without expression, as a CALCULATE modifier
DEFINE
COLUMN '__V'[Parent] = COLLAPSE ( [Sales Amount], ROWS )
COLUMN '__V'[Grandparent] = COLLAPSE ( [Sales Amount], ROWS, 2 )
COLUMN '__V'[QuarterOrSelf] = COLLAPSE ( [Sales Amount], [Month] )
COLUMN '__V'[PctOfParent] =
DIVIDE ( [Sales Amount], CALCULATE ( [Sales Amount], COLLAPSE ( ROWS ) ) )
VAR __Core =
SUMMARIZECOLUMNS (
ROLLUPADDISSUBTOTAL (
'Date'[Calendar Year Number], "IsGrandTotalRowTotal",
'Date'[Calendar Year Quarter Number], "IsYearTotal",
'Date'[Calendar Year Month Number], "IsQuarterTotal"
),
TREATAS ( { 2007, 2008 }, 'Date'[Calendar Year Number] ),
TREATAS ( { 1, 2, 3, 4, 5, 6 }, 'Date'[Month Number] ),
"Sales_Amount", [Sales Amount]
)
VAR __Input =
SELECTCOLUMNS (
__Core,
"Year", 'Date'[Calendar Year Number],
"Quarter", 'Date'[Calendar Year Quarter Number],
"Month", 'Date'[Calendar Year Month Number],
"IsGrandTotalRowTotal", [IsGrandTotalRowTotal],
"IsYearTotal", [IsYearTotal],
"IsQuarterTotal", [IsQuarterTotal],
"Sales Amount", [Sales_Amount]
)
TABLE '__V' = __Input
WITH VISUAL SHAPE
AXIS ROWS
GROUP [Year] TOTAL [IsGrandTotalRowTotal]
GROUP [Quarter] TOTAL [IsYearTotal]
GROUP [Month] TOTAL [IsQuarterTotal]
ORDER BY [Year] ASC, [Quarter] ASC, [Month] ASC
DENSIFY "IsDensifiedRow"
EVALUATE
SELECTCOLUMNS (
'__V',
"Year", [Year],
"Quarter", [Quarter],
"Month", [Month],
"Sales Amount", [Sales Amount],
"Parent", [Parent],
"Grandparent", [Grandparent],
"QuarterOrSelf", [QuarterOrSelf],
"% of Parent", FORMAT ( [PctOfParent], "0.00%" )
)
ORDER BY [Year], [Quarter], [Month]
| Year | Quarter | Month | Sales Amount | Parent | Grandparent | QuarterOrSelf | % of Parent |
|---|---|---|---|---|---|---|---|
| (Blank) | (Blank) | (Blank) | 10,247,701.35 | 10,247,701.35 | 10,247,701.35 | 10,247,701.35 | 100.00% |
| 2007 | (Blank) | (Blank) | 5,693,275.41 | 10,247,701.35 | 10,247,701.35 | 5,693,275.41 | 55.56% |
| 2007 | 20071 | (Blank) | 2,646,673.39 | 5,693,275.41 | 10,247,701.35 | 2,646,673.39 | 46.49% |
| 2007 | 20071 | 200701 | 794,248.24 | 2,646,673.39 | 5,693,275.41 | 2,646,673.39 | 30.01% |
| 2007 | 20071 | 200702 | 891,135.91 | 2,646,673.39 | 5,693,275.41 | 2,646,673.39 | 33.67% |
| 2007 | 20071 | 200703 | 961,289.24 | 2,646,673.39 | 5,693,275.41 | 2,646,673.39 | 36.32% |
| 2007 | 20072 | (Blank) | 3,046,602.02 | 5,693,275.41 | 10,247,701.35 | 3,046,602.02 | 53.51% |
| 2007 | 20072 | 200704 | 1,128,104.82 | 3,046,602.02 | 5,693,275.41 | 3,046,602.02 | 37.03% |
| 2007 | 20072 | 200705 | 936,192.74 | 3,046,602.02 | 5,693,275.41 | 3,046,602.02 | 30.73% |
| 2007 | 20072 | 200706 | 982,304.46 | 3,046,602.02 | 5,693,275.41 | 3,046,602.02 | 32.24% |
| 2008 | (Blank) | (Blank) | 4,554,425.94 | 10,247,701.35 | 10,247,701.35 | 4,554,425.94 | 44.44% |
| 2008 | 20081 | (Blank) | 1,816,385.21 | 4,554,425.94 | 10,247,701.35 | 1,816,385.21 | 39.88% |
| 2008 | 20081 | 200801 | 656,766.69 | 1,816,385.21 | 4,554,425.94 | 1,816,385.21 | 36.16% |
| 2008 | 20081 | 200802 | 600,080.00 | 1,816,385.21 | 4,554,425.94 | 1,816,385.21 | 33.04% |
| 2008 | 20081 | 200803 | 559,538.52 | 1,816,385.21 | 4,554,425.94 | 1,816,385.21 | 30.81% |
| 2008 | 20082 | (Blank) | 2,738,040.73 | 4,554,425.94 | 10,247,701.35 | 2,738,040.73 | 60.12% |
| 2008 | 20082 | 200804 | 999,667.17 | 2,738,040.73 | 4,554,425.94 | 2,738,040.73 | 36.51% |
| 2008 | 20082 | 200805 | 893,231.96 | 2,738,040.73 | 4,554,425.94 | 2,738,040.73 | 32.62% |
| 2008 | 20082 | 200806 | 845,141.60 | 2,738,040.73 | 4,554,425.94 | 2,738,040.73 | 30.87% |
Related articles
Learn more about COLLAPSE in the following articles:
-
Introducing VISUAL SHAPE for visual calculations in Power BI
This article introduces the VISUAL SHAPE clause, which defines a hierarchical structure for a table used in visual calculations. » Read more
-
Introducing EXPAND and COLLAPSE for visual calculations in Power BI
This article introduces the two basic visual context navigation functions: EXPAND and COLLAPSE. » Read more
-
Using EXPAND and COLLAPSE in visual calculations
This article provides examples of visual calculations where the use of EXPAND and COLLAPSE is required to obtain the correct result. » Read more
-
Dynamic Pareto analysis in Power BI
This article describes how to implement a dynamic Pareto calculation in Power BI based on a measure that can be selected from a slicer and dynamically filtered by other slicers in the report. » Read more
-
Using visual calculations to highlight an entire row
Visual calculations can be used efficiently to format visuals. This article presents an interesting technique to highlight a row based solely on the maximum value in the last column. » Read more
-
Dynamic formatting by hierarchy level with ISINSCOPE and ISATLEVEL
This article describes how to apply different formatting rules at each level of a hierarchy (one rule at the year level, another at the quarter level, another at the month level) using ISINSCOPE in a measure or ISATLEVEL in a visual calculation. » Read more
Last update: Nov 2, 2026 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://learn.microsoft.com/en-us/dax/collapse-function-dax