Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |