Hide columns in matrix visual

[Anonymous]

Hi,

I have a matrix visual that contains the following columns:

Country	Share	Share MoM%	Share YoY%	Sales	Sales MoM%	Sales YoY%	Another metric	MoM%	YoY%
USA	20%	2.0	5.0	1,000	+10%	+20%	100	10%	20%
Germany	20%	2.0	5.0	1,000	+10%	+20%	100	10%	20%

And to be honest, it actually contains a lot more columns. I have 4 more metrics that also show the MoM% and YoY% results, which leads to a total of 22 columns in one matrix.

The country column is a dimension. All of the other columns are separate measures based on millions of rows of data.

There’s way too many columns which makes it difficult for a user to understand what they are actually looking at. I’m looking for a possibility to make it possible for users to hide or show certain columns. I know that using the SWITCH function you can create 1 metric that shows all of the data for all columns.. But what I run into with this is that it makes it very slow to load the matrix, especially when all 22 columns are visible, and when there’s also sub-dimensions added it can take half a minute or longer before the matrix is fully visible on the screen.

Another thing I tried was to simply create a new table with two values: "MoM% off" and "MoM% on". I then created a filter with those two values. I then updated all MoM% metrics to return a blank value if the value is set to "MoM% off". If there's a trick to automatically hide a column when all values are blank, that would be a solution as well.

Is there an other way to for instance quickly hide all the MoM% and YoY% columns, or to only show all columns that have to do with Sales?

Thanks

Bas

[MFelix]

Hi @Anonymous ,

Looking at your information and has you say theres is a lot of columns for a matrix, and if you are having performance issues with the switch function, one workaroudn can be to create bookmarks.

Basically create a replica of your matrix visualizations with the values you need and then use the bookmarks to show the correct matrix based on button selection.

[Anonymous]

Hi @MFelix ,

Thank you for looking into this.

Bookmarks could indeed be an option, but I already have various bookmarks in place that are used to show or hide visuals. I'm looking for a solution that either works via DAX or via changing any of the settings of my matrix.

Best regards

Bas

[MFelix]

The trick with DAX is using the switch function and disconnected table but you refer there are performance issues.

How did you setup the measure and matrix?