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 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.
- Anonymous2 years agoNot applicable
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.