Forum Discussion
Expand Matrix table all level in hierarchy - Always Visible while changing Column using Parameter
- 2 years ago
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 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.