COLLAPSEALL DAX Function (Visual calculations)

Syntax  |  Return values  |  Remarks  |  Examples  |  Articles  

Retrieves a context with removed detail levels along an axis. With an expression, returns its value in the new context, enabling navigation to the highest level on the axis and is the inverse of EXPANDALL.

Syntax

COLLAPSEALL ( [<Expression>], <Axis> )
Parameter Attributes Description
Expression Optional

The expression to be evaluated in the new context.
Axis

An axis reference.

Return values

Scalar A single value of any type.

The value of Expression at the top level (total) of Axis. Without Expression, COLLAPSEALL 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

COLLAPSEALL can be used only in a visual calculation. In a measure or in a calculated column, COLLAPSEALL returns an error.

COLLAPSEALL has two forms. With Expression, COLLAPSEALL moves the context to the top level of Axis (the total of the axis) and returns the value of Expression in the new context. Without Expression, COLLAPSEALL only moves the context. You can use this form only as a filter argument of CALCULATE, for example CALCULATE ( [Sales Amount], COLLAPSEALL ( ROWS ) ).

The Axis argument is mandatory and must be an axis reference, such as ROWS or COLUMNS. A column reference is not valid and returns an error.

COLLAPSEALL moves only on the axis that you specify. When the visual has both ROWS and COLUMNS, use COLLAPSEALL two times to get the grand total, for example COLLAPSEALL ( COLLAPSEALL ( [Sales Amount], ROWS ), COLUMNS ). If the visual does not have the axis, COLLAPSEALL does not move.

COLLAPSE moves up one level at a time. COLLAPSEALL gives the same result as COLLAPSE with an N value that is equal to or larger than the number of levels of the axis. EXPANDALL does the opposite of COLLAPSEALL: it moves to the lowest level of the axis.

» 5 related articles

Examples

--  COLLAPSEALL moves the visual calculation to the top level of the axis.
--  The query simulates a matrix with Year, Quarter, and Month on rows.
--  COLLAPSEALL returns the grand total on every row.
--  COLLAPSE moves up only one level.
--  % of Total uses COLLAPSEALL without expression, as a CALCULATE modifier
DEFINE
    COLUMN '__V'[CollapseAll] = COLLAPSEALL ( [Sales Amount], ROWS )
    COLUMN '__V'[Collapse] = COLLAPSE ( [Sales Amount], ROWS )
    COLUMN '__V'[PctOfTotal] =
        DIVIDE ( [Sales Amount], CALCULATE ( [Sales Amount], COLLAPSEALL ( 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],
    "COLLAPSEALL", [CollapseAll],
    "COLLAPSE", [Collapse],
    "% of Total", FORMAT ( [PctOfTotal], "0.00%" )
)
ORDER BY [Year], [Quarter], [Month]
Year Quarter Month Sales Amount COLLAPSEALL COLLAPSE % of Total
(Blank) (Blank) (Blank) 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 55.56%
2007 20071 (Blank) 2,646,673.39 10,247,701.35 5,693,275.41 25.83%
2007 20071 200701 794,248.24 10,247,701.35 2,646,673.39 7.75%
2007 20071 200702 891,135.91 10,247,701.35 2,646,673.39 8.70%
2007 20071 200703 961,289.24 10,247,701.35 2,646,673.39 9.38%
2007 20072 (Blank) 3,046,602.02 10,247,701.35 5,693,275.41 29.73%
2007 20072 200704 1,128,104.82 10,247,701.35 3,046,602.02 11.01%
2007 20072 200705 936,192.74 10,247,701.35 3,046,602.02 9.14%
2007 20072 200706 982,304.46 10,247,701.35 3,046,602.02 9.59%
2008 (Blank) (Blank) 4,554,425.94 10,247,701.35 10,247,701.35 44.44%
2008 20081 (Blank) 1,816,385.21 10,247,701.35 4,554,425.94 17.72%
2008 20081 200801 656,766.69 10,247,701.35 1,816,385.21 6.41%
2008 20081 200802 600,080.00 10,247,701.35 1,816,385.21 5.86%
2008 20081 200803 559,538.52 10,247,701.35 1,816,385.21 5.46%
2008 20082 (Blank) 2,738,040.73 10,247,701.35 4,554,425.94 26.72%
2008 20082 200804 999,667.17 10,247,701.35 2,738,040.73 9.76%
2008 20082 200805 893,231.96 10,247,701.35 2,738,040.73 8.72%
2008 20082 200806 845,141.60 10,247,701.35 2,738,040.73 8.25%

Related articles

Learn more about COLLAPSEALL in the following articles:

Last update: Oct 28, 2026   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo

Microsoft documentation: https://learn.microsoft.com/en-us/dax/collapseall-function-dax

2018-2026 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us   » Privacy Policy & Cookies

Context Transition

This function performs a Context Transition if called in a Row Context. Click to read more.

Row Context

This expression is executed in a Row Context. Click to read more.

Passed by Expression

This parameter is passed by expression and can be evaluated multiple times in different evaluation contexts.

Iterator

Not recommended

The use of this function is not recommended. See Remarks and Related functions for alternatives.

Not recommended

The use of this parameter is not recommended.

Deprecated

This function is deprecated. Jump to the Alternatives section to see the function to use.

Volatile

A volatile function may return a different result every time you call it, even if you provide the same arguments. Click to read more.

Deprecated

This parameter is deprecated and its use is not recommended.

DirectQuery compatibility

Limitations are placed on DAX expressions allowed in measures and calculated columns.
The state below shows the DirectQuery compatibility of the DAX function.

Contribute

Want to improve the content of COLLAPSEALL? Did you find any issue?
Please, report it us! All submissions will be evaluated for possible updates of the content.


This site is protected by reCAPTCHA.