cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

Hide columns in matrix visual

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

3 REPLIES 3
Super User III
Super User III

Hi @basroozen ,

 

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





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

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? 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors