Forum Discussion
Hide/Show Column in Table Visual Matrix on Report
Hello,
I want to show/hide column in table visual matrix based on some action on reports.
Eg, There are 3 olumns (Id, Value , Percent of Value) now I want to hide value column based on some filter action on report or show on some action of filter
Output:
There might be two case 1) Show Value, 2) Hide Value
If user select Show value then Value column will display
Else user select Hide value then Value will be hide.
Is there any way to do this ?
Let me know anything else you would like to understand
Thanks,
37 Replies
I don't believe this is currently possible. You can probably hide the results (e.g. Values) but not the column headings.
Thats completely fine if we can not hide the header of table but how can we hide the entire column value in table matrix ?
Can you please suggest the way how to do it.
I reckon you can but you have to be quite tricky with DAX and pivot your data.
So the underlying table would have a column that represented the axis to be added to Columns
something like
ColType Actual Value
--------- -------------
ID 1
Value 20
Percent of Value 15
ID 2
Value 50
Percent of Value 18
Then you could add [colType] to columns and use a new dynamic dax based measure to only return a value based on a filter selection
I think the only downside is you are stuck with a single data formatting setting so couldn't have whole number and percent.
I can mock something up if interested.
Hi,
Would really love to know how this can be achieved. What I would require is a table with 15 columns. And according to a multi-select drop-down, I would want to show/hide the columns. In the drop-down, the column names would be listed.
Hi anandsoftweb,
Based on my research, there is no such a column visibility option supported by current version of Power BI report. I am afraid your requiremnet to show/hide column dynamically cannot be achieved.
It is only allowed to hide detailed values under column, but column headers are always appear even no data values are displayed. You can refer to this link to get more information about this workaround.
If you have any concern on this feature, you can submit your idea on the link below. If this feature was mentioned by many users, product team will consider to add this feature to next release. https://ideas.powerbi.com/forums/265200-power-bi-ideas
Thanks,
Yuliana GuI am fine if header is there but value should be hide any how is it possible using DAX or any calculation some workaround is also good.
How about you use Excel to build an example of what you want to do, take a screen shot and post it back here. It is hard for me to understand what you want.
I do this quite often for displaying dynamic measures in tables and matrixes.
The way you go about it is create 2 measures.
The first one that you use for calculating what you need to be displayed, lets call it Display
Display=calculate(sum(F_Table[values]))
you can create another measure that is used only for hiding stuf, so you want it to always display blanks, lets call it Hide
Hide=blank()
Now you need the main measure that you will actually use in the visualisation, and you need a slice.
I would sugest you create a table containing 2 rows and call it Measure_Slicer and a collumn named lets say Slicer_Col. The easiest way to do it is from Home tab you click Enter Data and you just type manually
in the first row type: Show values
in the secon row type: Hide values
and thats your table that will be used to populate our slice. You create a slicer on your report and as selection put Slicer_Col from your new table Measure_Slicer.
Now you need a way to capture the slicer value. For that reason we create a new measure, I just call it measure_slicer
measure_slicer=if(hasonevalue(Measure_Slicer[Slicer_Col]);values(Measure_Slicer[Slicer_Col]);blank())
the calculate measure measure_slicer is then used as a variable would be used in other programs. Your measure_slicer will always display the selected value, so you can use it to filter out other measures.
You now have to make the main measure, lets simply call it Measure. This measure will check our measure_slicer and display data acordingly.
Measure=Switch([measure_slicer];"Show values";[Display];"Hide values";[Hide])
Thats all. You use the new created Measure in any visualisation you want and it will always display values based on selection in your slicer.
Hope it helps and is easy enough to understand :)
good luck
josipinho yes this is a great approach, but it doesn't solve the problem. The OP wanted to hide the column. Your approach keeps the column but hides the values.
But actually, there IS a solution to this now - using bookmarks and the selection pane delivered in Oct 17. Here is the process.
- Create 2 copies of the table, one with the column and one without the column.
- Turn on bookmarks preview feature (file\options)
- Turn on the bookmarks pane and the selection pane
- Create 2 bookmarks. One has one of the tables visible and the other has the other table visible
- Add 2 images to act as the toggle and then assign one of the bookmarks to each.
Watch this video from Patrick at GuyInACube for a demo https://www.youtube.com/watch?v=_Afcj8mT5_Q
He did say this: "Thats completely fine if we can not hide the header of table but how can we hide the entire column value in table matrix ?"
But you are right, bookmarks are another way to do it aswell.
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.
This option is possible in QlickSense, but unfortunately not in Power BI.
Like in QlickSense you could then work with a loose slicer: the user can show/hide the columns of his choice.
So sad to see that this is still not possible in Power BI 😞
Not a single workaround mentioned in the posts works with my data (dataset is too large, too many hiearchy levels, ...).
Not to sound too harsh or bitter, but I have basically stopped using PBI. Not just because of this, but this and countless other things that "couldn't be done" and/or required "workarounds", etc.
I'm defintely not a newb when it comes to this but I feel PBI isn't ready for primer time. I decided to wait a while (long while, it seems) until is more mature.
What things can't you do without "work arounds" that have caused you to walk away?
It is now possible to have columns with different formatting - it was released last month.
