Expand Matrix table all level in hierarchy - Always Visible while changing Column using Parameter

[Anonymous]

Hi,

I am using parameter/Slicer to change first column header from Quarter to Year values, I also added second and third header columns after time period column by turning off +- and stepped layout on row headers .

I noticed when I expand selection to the last level of hirarchy on Quarter period selection and then if I change this to Year selection, it automatically collapse column to first column and hide other two column header. I always have to click on top expand all level down to hierarchy to expand.

It's very confusing for viewers as they always have to click on expand hierachy button on top of matrix.

I published online after expanding on quareter, but when viewers change this to year it automatically collapse. I don't want to use bookmarks and switch axis in this case. Is there any other way, we can make all columns visible always rather than collapsing.

[MarkLaf · Accepted solution]

This can be achieved but you need to construct a helper table that puts all your years and quarters into a single column and then performs a calculate depending on type (quarter vs. year). This allows a filter to just operate on a single field, which won't reset matrix visuals (unlike a parameter, which is switching out fields, thus altering the matrix structure and causing a reset).

Here are steps I took to achieve the above.

Table

Date	Person	Sales	Campaign
1/1/2024	Matt	10	Winter
1/15/2024	Matt	15	Winter
1/20/2024	Amy	15	Winter
2/3/2024	Amy	5	Winter
2/25/2024	Matt	20	Winter
2/26/2024	Amy	10	Winter
7/1/2024	Matt	10	Summer
7/15/2024	Matt	5	Summer
7/20/2024	Amy	20	Summer
8/1/2024	Amy	10	Summer
8/25/2024	Amy	5	Summer
1/1/2023	Matt	10	Winter
1/15/2023	Matt	10	Winter
1/20/2023	Amy	10	Winter
2/3/2023	Amy	5	Winter
2/25/2023	Amy	20	Winter
2/26/2023	Matt	10	Winter
7/1/2023	Matt	5	Summer
7/15/2023	Amy	10	Summer
7/20/2023	Amy	15	Summer
8/1/2023	Amy	15	Summer
8/25/2023	Amy	5	Summer

Dates (mark as date table)

Dates = 
GENERATE(
    CALENDAR( DATE( YEAR( MIN( 'Table'[Date] ) ), 1, 1 ), DATE( YEAR( MAX( 'Table'[Date] ) ), 12, 31 ) ),
    VAR _dt = [Date] RETURN
    ROW(
        "Year", YEAR( _dt ),
        "YearTxt", FORMAT( _dt, "YYYY" ),
        "QuarterNum", QUARTER( _dt ),
        "Quarter", "Q" & QUARTER( _dt )
    )
)

Output:

DateHierarchySelect (helper table)

DateHierarchySelect = 
VAR _yrs = 
    GENERATE( 
        //using selectcolumns so column name is Period instead of YearTxt
        SELECTCOLUMNS( DISTINCT( Dates[YearTxt] ), "Period", Dates[YearTxt] ) , 
        ROW( "Period Scale", "Year" ) 
    )
VAR _qts = 
    GENERATE( 
        DISTINCT( Dates[Quarter] ), 
        ROW( "Period Scale", "Quarter" ) 
    )
RETURN
UNION( _yrs, _qts )

Output:

Relationships

Create a measure that iterates through DateHierarchySelect[Period Scale] and provides the scale-specific calc. In this instance we are just adding Sales. You may need a different iterator and different calculation depending on your objectives, but I think this provides the gist.

Sales_DynamicPeriod = 
SUMX( 
    VALUES( DateHierarchySelect[Period Scale] ), 
    SWITCH( 
        DateHierarchySelect[Period Scale], 
        "Year", 
            CALCULATE( 
                SUM( 'Table'[Sales] ), 
                TREATAS( VALUES( DateHierarchySelect[Period] ), Dates[YearTxt] ) 
            ),
        "Quarter", 
            CALCULATE( 
                SUM( 'Table'[Sales] ), 
                TREATAS( VALUES( DateHierarchySelect[Period] ), Dates[Quarter] ) 
            )
    )
)

Now, put together your slicer on DateHierarchySelect[Period Scale] and matrix with all levels expanded.

[Anonymous]

@Anonymous  Thank you very much for your reply. I think that's the main challenge of default Matrix visual added in power BI, Its limited to some functionalities.
In the matrix, if we put a field with a hierarchy structure, it will not be expanded by default at the beginning, and dashboard users have to click on the drill-down button in the upper right corner to expand hierarchy each time. We can add toggle +- sign on the side otherwise user will get confuse, if they dont see +- on side and every time view willl collapse to default view.
I already searched other additional table / matrix visual, all of them ask for license to buy. For now I will add +- sign on the side and leave this on users to click on drill down level after changing the view each time.

[Anonymous]

@Anonymous Thank you for your reply, I already turn stepped layout off. I want to show all level of hierarchy into a matrix by default but my issue is it won't expand all levels by default.

[Anonymous]

Hi, @Anonymous 

Thank you very much for your reply. In the matrix, if you put a field with a hierarchy structure, it will not be expanded by default at the beginning, and you will need to click the drill-down button in the upper right corner to expand your hierarchy.
If the default matrix doesn't meet your needs, you can look for visuals in the visual marketplace that fit your situation.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[Anonymous]

Hi, @Anonymous 

I think you can try turning off the stepped layout of the matrix:

For more details about the matrix, you can click on the link below:

Create a matrix visual in Power BI - Power BI | Microsoft Learn

If you put a hierarchy into a matrix, it won't expand all levels by default, you need to click on 

button to expand.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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