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.
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.