Forum Discussion
Hide/Show Column in Table Visual Matrix on Report
Is there any update on this problem?
If not, it really frustrating - I have a simple table (5 rows, minimal calculations) but with numerous Year columns (2003-2017); I would like to present the user with a range slider that will result in the report displaying only a user-selected range of Years (for example, 2005-2009).
Is there a simple way of doing it?
The easy way to have the years show, or not show, is to have a good data model design. If you have 1 column of data for every year in the underlying source table, this won’t work. This is also not a good design. Instead you should unpivot the data into 2 columns, one that states the year and the other that has the values. Then you place the year column from the table I to the columns in the matrix, and it will do what you want.
Thanks; I'll try it for this table and report back.
More generally, I've heard of the concept of "long and thin" data; the problem I see with that approach, though, is that if you have a table with many rows, the "long" part of the table becomes very long and unmanageable. It's much easier to work with the pivot table - so I guess that in that case, you have to wait until absolutely the last moment before you unpivot it.
What do you mean “unmanageable”? Power BI is a database. It is not intended that you work with the source data. The idea is that you load the data into an appropriate table structure (data model) and then interact via visuals. I have data models with 100 million rows of data and it is perfectly manageable.
MattAllington I currently have my data structured "tall and thin" and am using a matrix visual to achieve the "wide" view in Power BI which allows users to select / de-select which metrics (shown as columns) they want to view, but they have the additional requirement that they need to be able to export the table and have it look the same, which, as far as I can tell, requires that my underlying data be "flat". Once I do this, they lose the ability to select/de-select metrics, and the volume of metrics/combinations that a use could select is too many to use the bookmark option.
Any suggestions for alternative solutions?
Why do they need to export the data to look at it? Can it be seen in power BI? What do they get that is different when exported that they don’t get in power BI? Have you considered using Analyze in Excel?
