Daily DAX : Day 142 COLLAPSEALL

 The COLLAPSEALL function in Power BI's Data Analysis Expressions (DAX) language is a visual calculation function designed to navigate and manipulate the context of hierarchical data within a visual, such as a matrix or table. Introduced as part of the visual calculations feature in Power BI, it allows users to "collapse" the visual context to the highest level of a specified hierarchy or axis, effectively removing all lower-level details. This function is particularly useful when working with hierarchical data structures (e.g., year > quarter > month) and performing calculations that need to reference aggregated values at the topmost level.

Syntax

There are two primary forms of the COLLAPSEALL function:

    With an Expression (Returns a Value):

    COLLAPSEALL(<expression>, <axis>)

        <expression>: The calculation or measure you want to evaluate (e.g., [SalesAmount]).

        <axis>: The axis in the visual (e.g., ROWS or COLUMNS) whose hierarchy you want to collapse to the top level.

        Returns: The value of the expression evaluated at the highest level of the hierarchy on the specified axis.

    Without an Expression (Navigation Only):

    COLLAPSEALL(<axis>)

        <axis>: The axis to collapse.

        Returns: Modifies the evaluation context without returning a value directly; typically used within a CALCULATE function.

How It Works

    COLLAPSEALL moves the visual context to the topmost level of the hierarchy defined for the specified axis (e.g., ROWS or COLUMNS in a matrix visual).

    The hierarchy is determined by the columns present in the axis, as defined by the visual's structure or the VISUAL SHAPE clause (a feature that specifies hierarchical relationships).

    When used with an expression, it calculates that expression at this top level. Without an expression, it adjusts the context for subsequent calculations.

Use Case

Imagine you have a Power BI matrix visual displaying sales data with a hierarchy on the rows: Total > Year > Quarter > Month. You want to calculate the percentage of each level's sales relative to the grand total (the "Total" level), regardless of the current level being displayed.

Example Scenario

    Data: A table summarizing total sales with a hierarchy: Total > Year > Quarter > Month.

    Visual: A matrix with this hierarchy on the rows and [SalesAmount] as the value.

    Goal: Show the percentage of sales at each level (e.g., Year, Quarter, Month) compared to the overall total sales.

DAX Calculation

You could create a measure like this:

Sales % of Total = 

DIVIDE(

    [SalesAmount],

    CALCULATE(

        [SalesAmount],

        COLLAPSEALL(ROWS)

    )

)

    [SalesAmount]: The sales value at the current level (e.g., a specific month, quarter, or year).

    COLLAPSEALL(ROWS): Moves the context to the highest level of the row hierarchy ("Total"), ignoring all filters from lower levels (Year, Quarter, Month).

    CALCULATE: Evaluates [SalesAmount] at this collapsed level (the grand total).

    DIVIDE: Computes the ratio of the current level's sales to the grand total.

Result in the Matrix

Row Hierarchy

SalesAmount

Sales % of Total

Total

10,000

100%

2024

6,000

60%

Q1 2024

2,000

20%

Jan 2024

700

7%

Feb 2024

600

6%

Q2 2024

4,000

40%

    At the "Total" level, Sales % of Total is 100% because it compares 10,000 to itself.

    At "2024," it’s 60% (6,000 / 10,000).

    At "Jan 2024," it’s 7% (700 / 10,000).

Key Points

    Context Navigation: COLLAPSEALL is the inverse of EXPANDALL, which moves to the lowest level of the hierarchy. Together, they provide powerful tools for navigating hierarchical data in visuals.

    Visual Dependency: This function only works within visual calculations, meaning it relies on the structure of the visual (e.g., a matrix or table) and the defined hierarchy.

    Practical Application: It’s ideal for percentage-of-total calculations, comparisons to a grand total, or any scenario where you need to reference the top-level aggregate without manually removing filters.

When to Use

    Use COLLAPSEALL when you need to perform calculations relative to the highest level of a hierarchy, such as:

        Percentages of a grand total.

        Ratios against an overall benchmark.

        Aggregations that ignore lower-level filters in a visual.

In summary, COLLAPSEALL simplifies working with hierarchical data in Power BI visuals by allowing you to jump to the top of the hierarchy effortlessly, making it a valuable tool for dynamic and context-aware calculations.