Forum Discussion
Hide/Show Column in Table Visual Matrix on Report
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.
Hi Matt,
In a matrix visual, is there a way to dynamically hide blank columns? Thanks!
Best regards,
Ferdinand
I think this thread has now come full circle. This was the original question back on page 1. There is no way that I know of to do this. You could use Analyze in Excel and then build a pivot table. From there you can go into Fields, Items and Sets and build what you want. This is on the Analyze menu from memory. Create a new set and manually remove the columns you don’t need. But it will not be dynamic.
Now that I am sitting here drinking my morning coffee, I think you could do this with a table function and a switch measure. In short, you would write a query that returns a list of column headers you need, the result value (maybe, maybe not) and a sort column. Then write a switch measure.
Edit:
I would be willing to give it a go if someone provides some realistic (non sensitive) data set up in a matrix in a pbix file.Scrap that. The values across that columns have different formatting. This could be done with text, but I think it is sub optimal. I am back to “can’t be done”, or at least “can’t be done to an acceptable level”