How to collapse selected columns and keep the rest expanded

[NarekGrigoryan]

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

[Anonymous]

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]

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]

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.