Native Matrix Visual Glitch - Expand/Collapse buttons not working

[PedroMorbey]

Hello,

I currently have a DAX measure that's triggering quite the weird behaviour on the native Matrix visual.

I'm using 4 fields which belong to the same table (Dim_Product) on the Rows and that measure on Values.

The measure is apparently returning the correct values, but it's disabling the Expand/Collapse button functionality of the visual.

Example:

1. This is the state of the visual without any field expansion:

2. This is how it shows itself when I try to expand the first item on the visual using the toggle button:

As you can see above, the button state changes (from [+] to [-]), but no related rows expand below. However, when we use the "Expand all down one level in the hierarchy" Header Icon, it does display all the expanded rows from the 2nd field in the Rows hierarchy with the correct values:

Is this behaviour something that you've ever heard of and know how to deal with?

For reference, I'm using a fairly simple snowflake schema with one Fact table and the measure that's triggering the behaviour I've just described is the following:

9Box_Cluster_NS_by_Formulation = 
// 9-Box Thresholds
// Net Sales Absolute Thresholds
VAR _Net_Sales_Absolute_Low_Threshold = [NS_Or_Vol_Absolute_Lower_Threshold]
VAR _Net_Sales_Absolute_High_Threshold = [NS_Or_Vol_Absolute_Higher_Threshold]
// Net Sales Relative Thresholds
VAR _Net_Sales_Relative_Low_Threshold = [NS_Or_Vol_Relative_Lower_Threshold]
VAR _Net_Sales_Relative_High_Threshold = [NS_Or_Vol_Relative_Higher_Threshold]

// Threshold Type
VAR _Threshold_Type = SELECTEDVALUE( 'AUX_9Box_Y_Axis_Threshold_Type'[Threshold Type] )

// Gross Margin Thresholds
VAR _Gross_Margin_High_Threshold = [Gross_Margin_Higher_Threshold]
VAR _Gross_Margin_Low_Threshold = [Gross_Margin_Lower_Threshold]

// Base Tables
VAR _Base_Table =
FILTER(
    CALCULATETABLE (
            ADDCOLUMNS (
                SUMMARIZE ( 
FCT_Sales_by_GMN_and_ProfitCentre, 'DIM_Product'[Formulation] ),
                "@Net Sales", [Net Sales],
                "@Gross Margin", [% Gross Margin]
            ),
            ALLSELECTED ( 'DIM_Product' ),
            ALLSELECTED ( 'DIM_Production_Technology_Group'),
            ALLSELECTED ( DIM_Site ),
            ALLSELECTED ( DIM_Profit_Centre ),
            ALLSELECTED ( 'Date' ),
            ALLSELECTED ( DIM_Product_Status ),
            ALLSELECTED ( DIM_BU )
        ),
    NOT( 'DIM_Product'[Formulation]  IN {"Cx Only","N/A", "0", "Missing Formulation", "Multiple Formulation", "No Formulation"} )
    )
    
VAR AllSales =
    SUMX( _Base_Table, [@Net Sales] )
    
VAR _Final_Table =
// Add the cumulative percentage of Net Sales
    ADDCOLUMNS (
        _Base_Table,
        "@CumulatedPct",
        VAR CurrentSalesAmt = [@Net Sales]
        VAR CumulatedSales =
            FILTER (
                _Base_Table,
                [@Net Sales] >= CurrentSalesAmt
            )
        VAR CumulatedSalesAmount =
            SUMX (
                CumulatedSales,
                [@Net Sales]
            )
        VAR Perc =
            DIVIDE (
                CumulatedSalesAmount,
                AllSales
            )
        RETURN
            MIN ( Perc, 1 ) -- Avoid >100% in case of rounding issues
    )
    
// Filter tables
    VAR _Filter_HighHigh =
        FILTER(    
            _Final_Table,
            IF(
                "High x High" IN VALUES(AUX_9Box_Cluster_Performance_Slicer[Performance Cluster]),
                IF( _Threshold_Type = "Absolute",
                    _Net_Sales_Absolute_High_Threshold < [@Net Sales] && _Gross_Margin_High_Threshold < [@Gross Margin],
                    [@CumulatedPct] < _Net_Sales_Relative_High_Threshold  && _Gross_Margin_High_Threshold < [@Gross Margin]
                    ),
                False
                )
            )
    VAR _Filter_HighMedium = 
        FILTER(
            _Final_Table,
            IF(
                "High x Medium" IN VALUES(AUX_9Box_Cluster_Performance_Slicer[Performance Cluster]),
                IF( _Threshold_Type = "Absolute",
                    _Net_Sales_Absolute_High_Threshold < [@Net Sales] && ((_Gross_Margin_Low_Threshold <= [@Gross Margin]) && ([@Gross Margin] <= _Gross_Margin_High_Threshold)),
                    [@CumulatedPct] < _Net_Sales_Relative_High_Threshold && ((_Gross_Margin_Low_Threshold <= [@Gross Margin]) && ([@Gross Margin] <= _Gross_Margin_High_Threshold))
                    ),
                False
            )
        )
    VAR _Filter_HighLow = 
        FILTER(
            _Final_Table,
            IF(
                "High x Low" IN VALUES(AUX_9Box_Cluster_Performance_Slicer[Performance Cluster]),
                IF( _Threshold_Type = "Absolute",
                    _Net_Sales_Absolute_High_Threshold < [@Net Sales] && [@Gross Margin] < _Gross_Margin_Low_Threshold,
                    [@CumulatedPct] < _Net_Sales_Relative_High_Threshold && [@Gross Margin] < _Gross_Margin_Low_Threshold
                    ),
                False
            )
        )
    VAR _Filter_MediumHigh =
        FILTER(
            _Final_Table,
            IF(
                "Medium x High" IN VALUES(AUX_9Box_Cluster_Performance_Slicer[Performance Cluster]),
                IF( _Threshold_Type = "Absolute",
                    ((_Net_Sales_Absolute_Low_Threshold <= [@Net Sales]) && ([@Net Sales] <= _Net_Sales_Absolute_High_Threshold)) && _Gross_Margin_High_Threshold < [@Gross Margin],
                    (([@CumulatedPct] >= _Net_Sales_Relative_High_Threshold) && ([@CumulatedPct] <= _Net_Sales_Relative_Low_Threshold)) && _Gross_Margin_High_Threshold < [@Gross Margin]
                ),
                False
            )
        )
    VAR _Filter_MediumMedium =
        FILTER(
            _Final_Table,
            IF(
                "Medium x Medium" IN VALUES(AUX_9Box_Cluster_Performance_Slicer[Performance Cluster]),
                IF( _Threshold_Type = "Absolute",
                    ((_Net_Sales_Absolute_Low_Threshold <= [@Net Sales]) && ([@Net Sales] <= _Net_Sales_Absolute_High_Threshold)) && ((_Gross_Margin_Low_Threshold <= [@Gross Margin]) && ([@Gross Margin] <= _Gross_Margin_High_Threshold)),
                    (([@CumulatedPct] >= _Net_Sales_Relative_High_Threshold) && ([@CumulatedPct] <= _Net_Sales_Relative_Low_Threshold)) && ((_Gross_Margin_Low_Threshold <= [@Gross Margin]) && ([@Gross Margin] <= _Gross_Margin_High_Threshold))
                    ),
                False
            )
        )
   
    VAR _Filter_MediumLow =
        FILTER(
            _Final_Table,
            IF(
                "Medium x Low" IN VALUES(AUX_9Box_Cluster_Performance_Slicer[Performance Cluster]),
                IF( _Threshold_Type = "Absolute",
                    ((_Net_Sales_Absolute_Low_Threshold <= [@Net Sales]) && ([@Net Sales] <= _Net_Sales_Absolute_High_Threshold)) && ([@Gross Margin] < _Gross_Margin_Low_Threshold),
                    (([@CumulatedPct] >= _Net_Sales_Relative_High_Threshold) && ([@CumulatedPct] <= _Net_Sales_Relative_Low_Threshold)) && ([@Gross Margin] < _Gross_Margin_Low_Threshold)
                    ),
                False
            )
        )
    VAR _Filter_LowHigh =
        FILTER(
            _Final_Table,
            IF(
                "Low x High" IN VALUES(AUX_9Box_Cluster_Performance_Slicer[Performance Cluster]),
                IF( _Threshold_Type = "Absolute",
                    ([@Net Sales] < _Net_Sales_Absolute_Low_Threshold) && (_Gross_Margin_High_Threshold < [@Gross Margin]),
                    [@CumulatedPct] > _Net_Sales_Relative_Low_Threshold && (_Gross_Margin_High_Threshold < [@Gross Margin])
                ),
                False
            )
        )
    VAR _Filter_LowMedium =
        FILTER(
            _Final_Table,
            IF(
                "Low x Medium" IN VALUES(AUX_9Box_Cluster_Performance_Slicer[Performance Cluster]),
                IF( _Threshold_Type = "Absolute",
                    [@Net Sales] < _Net_Sales_Absolute_Low_Threshold && ((_Gross_Margin_Low_Threshold <= [@Gross Margin]) && ([@Gross Margin] <= _Gross_Margin_High_Threshold)),
                    [@CumulatedPct] > _Net_Sales_Relative_Low_Threshold && ((_Gross_Margin_Low_Threshold <= [@Gross Margin]) && ([@Gross Margin] <= _Gross_Margin_High_Threshold))
                  ),
                False
            )
        )
    VAR _Filter_LowLow = 
        FILTER(
            _Final_Table,
            IF(
                "Low x Low" IN VALUES(AUX_9Box_Cluster_Performance_Slicer[Performance Cluster]),
                IF( _Threshold_Type = "Absolute",
                    ([@Net Sales] < _Net_Sales_Absolute_Low_Threshold) && ([@Gross Margin] < _Gross_Margin_Low_Threshold),
                    [@CumulatedPct] > _Net_Sales_Relative_Low_Threshold && ([@Gross Margin] < _Gross_Margin_Low_Threshold)
                ),
                False
            )
        )

    VAR _final_filter = 
        UNION(
            _Filter_HighHigh,
            _Filter_HighMedium,
            _Filter_HighLow,
            _Filter_MediumHigh,
            _Filter_MediumMedium,
            _Filter_MediumLow,
            _Filter_LowHigh,
            _Filter_LowMedium,
            _Filter_LowLow
        )
        
-- Measure Return
RETURN
CALCULATE(
    [Net Sales],
    KEEPFILTERS(
        _final_filter
        )
    )     

P.S: I know the measure is convoluted, but optimizations aside, it is returning the intended values at all levels of the visual hierarchy.

P.S.2: This is happening with this measure, but it is not happening with other simpler measures, leading me to believe it must be the DAX concoction that's triggering the behaviour. Anyway, I'm in your hands 🙏

Best regards,

Pedro

[PedroMorbey · Accepted solution]

@v-yifanw-msft ,

Indeed, after trying to simplify/alter it to see some changes, I was able to arrive at the normal Matrix visual behaviour. I changed the intermediate virtual tables FILTER calculation from a nested IF to a regular boolean evaluation and it worked!

I went from this initial approach where the FILTER condition of the intermediate tables had some nested IFs:

// Filter tables
    VAR _Filter_HighHigh =
        FILTER(    
            _Final_Table,
            IF(
                "High x High" IN VALUES(AUX_9Box_Cluster_Performance_Slicer[Performance Cluster]),
                IF( _Threshold_Type = "Absolute",
                    _Net_Sales_Absolute_High_Threshold < [@Net Sales] && _Gross_Margin_High_Threshold < [@Gross Margin],
                    [@CumulatedPct] < _Net_Sales_Relative_High_Threshold  && _Gross_Margin_High_Threshold < [@Gross Margin]
                    ),
                False
                )
            )

To this simpler one:

// Filter tables
    VAR _Filter_HighHigh_Abs =
        FILTER(    
            _Final_Table,
            "High x High" IN _Selected_9Box_Clusters
            && _Threshold_Is_Absolute
            && _Net_Sales_Absolute_High_Threshold < [@Net Sales] && _Gross_Margin_High_Threshold < [@Gross Margin]
            )

This measure is far from being optimized, but for now it is working as intended without any major performance issues. Thank you for getting back to me.

Best regards

[v-yifanw-msft]

Hi @PedroMorbey ,

Based on the information you have provided, the expand/collapse functionality of a matrix visual object may be affected by the data model and the DAX metrics applied to it. There are several steps you can take to troubleshoot and resolve the issue:

1. Try simplifying the metric to see if the problem still exists. This helps determine if the complexity of the metric is indeed the root cause. You can start by breaking the metric down into smaller, simpler parts and testing the expand/collapse functionality of each part.
2. Ensure that the relationships between tables are correctly defined. Incorrect or inactive relationships may lead to unexpected behaviour of visual objects. For more details see:
   Relationship troubleshooting guidance - Power BI | Microsoft Learn
3. Try creating a simple explicit metric (e.g., sum of sales) and see if the expand/collapse function works as expected. This helps isolate the problem from the specific DAX metric you created. For more information on explicit and implicit metrics, see:
   Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.