Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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 ...
  • MarkLaf's avatar
    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

    DatePersonSalesCampaign
    1/1/2024Matt10Winter
    1/15/2024Matt15Winter
    1/20/2024Amy15Winter
    2/3/2024Amy5Winter
    2/25/2024Matt20Winter
    2/26/2024Amy10Winter
    7/1/2024Matt10Summer
    7/15/2024Matt5Summer
    7/20/2024Amy20Summer
    8/1/2024Amy10Summer
    8/25/2024Amy5Summer
    1/1/2023Matt10Winter
    1/15/2023Matt10Winter
    1/20/2023Amy10Winter
    2/3/2023Amy5Winter
    2/25/2023Amy20Winter
    2/26/2023Matt10Winter
    7/1/2023Matt5Summer
    7/15/2023Amy10Summer
    7/20/2023Amy15Summer
    8/1/2023Amy15Summer
    8/25/2023Amy5Summer

     

    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.