Forum Discussion

NarekGrigoryan's avatar
NarekGrigoryan
New Member
1 year ago

How to collapse selected columns and keep the rest expanded

Hi everyone!

 

I am working on a matrix viz where in columns I have my date. I am trying to get a view where years 2020-2023 will be collapsed to Quarter level (2020 Q1, 2020 Q2, 2020 Q3, ... , 2023 Q4) but 2024 has to be shown in monthly view. 

The date that I am using has date data type and I have the year-quarter-month hierarchy. Under the viz I have some buttons for drilling up/down, but they apply to the whole viz.

 

Have You encountered such problems, is it even solvable?

 

Thanks in advance

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, NarekGrigoryan 

    I am glad to help you.

     

    Because you didn't give a dataset for testing, I created a simple dataset myself:

     


    Then create a date table that contains all the dates for the Sales table:

     

    DateTable = 
ADDCOLUMNS(
    CALENDAR(MIN(Sales[Date]), MAX(Sales[Date])),
    "Year", YEAR([Date]),
    "MonthNumber", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "Quarter", "Q" & FORMAT(QUARTER([Date]), "0"),
    "YearQuarter", FORMAT([Date], "YYYY") & "-Q" & FORMAT(QUARTER([Date]), "0"),
    "YearMonth", FORMAT([Date], "YYYY") & "-" & FORMAT([Date], "MM")
)


    Create a calculated column to distinguish between the 2020-2023 display quarter and the 2024 display month:

     

    DisplayDate = 
IF(
    DateTable[Year] <= 2023,
    DateTable[YearQuarter],
    DateTable[YearMonth]
)


    Make sure that the relationship between the Sales table and the date table is established through the Date field:

     


    Drag Product to Rows of the matrix, Newly created calculated columns to Columns of the matrix, and SalesAmount to Values:

     


    I have attached the PBIX file of this simple example below, I hope it will be helpful to you.

     

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Fen Ling,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • NarekGrigoryan's avatar
      NarekGrigoryan
      New Member
      1 year ago

      Hi, Anonymous 

       

      Thanks for the solution provided.

      It will surely work only what I also want to have is the possibility for users to expand/collapse the view.

      So for example if the user wants to see 2023-Q4 in details he can expand it and see the monthly view.

       

      I can see that for rows there are +/- buttons and user can manually open what they want. But it seems that the same doesn't work for coulmns 😞

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi, NarekGrigoryan 

        Perhaps you could try dragging the Date directly to the Rows?
        But then the date hierarchy would be the same for all years:

         

         

         

        I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
        Best Regards,
        Fen Ling,
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.