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

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 37
RBear
Advocate I
Advocate I

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?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Not trying to be flip about it, but there were too many to count. You should understand that, initially, I really liked PBI and spent significant amounts of time and effort to learn it; so walking away wasn't done lightly.


If you want something, take for example the problem presented in this very thread- the thread is more than 3 years old! The problem, it seems, still is out there.


For another example, I just happened to receive (conincidelntally, I believe) a few hours after I have received this thread update, an update from another thread I was involved with. . That one is also more than 3 years old and despite the fact that it's marked as "Solved" it is still open and not solved! It may be entirely possible that this particular thread was the final straw for me: the fact that I could not just click an option to "prevent sorting" automatically was, and still is, absolutely astounding to me.


This is just off the top of my head. Just check in this forum and in other PBI-related forums and you'll see how many times the word "workaround" is being used. This isn't an emoji app on a cell phone - we should have to resort to tricks like that.

Everyone needs to make their own call if a product is right for them. But surely if there are too many issues for you to count, you should be able to mention, say just 2 more in addition to this thread. The one you posted above is not a work around. The default sort of every column is alpha numeric. There is a clearly defined and robust process to change the sort order - you simply specify the order you want, and then it will use that sort order. I'm not sure how else it could work. Power BU cannot just "know" the sort order you want. 

I'm just trying to understand what it is that makes the tool unworkable for you. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

I've been out of the loop for about two years, and since then a lot of water flowed under a lot of bridges, so I think I should be to be excused for not being able to come up with more examples.

But just to focus on the problem in that other thread: I always believed the product has to serve the user, not the other way around; so while I appreciate the fact that the "default sort of every column is alpha numeric" - the default is a problem for me as a user. So the product needs to either change the default to "not sorted", or offer a simple check-the-box option of "no sort". Please take look at Message 15 of that other thread and tell me if you sincerely believe that what is described there consititues "a clearly defined and robust process to change the sort order".

It's strange to me that, after all the time that has passed, I still feel greatly annoyed when I read that exchange...

So you stopped using Power BI 2 years ago - that was not clear in your post yesterday. It has progressed since then, for sure. 

Regarding the sort order "issue", Power BI is a database. Databases (all databases) have no concept of row order. All data is loaded and stored in a table, then retrieved when needed. The order the data is stored and retrieved is not important and is not tracked. In fact Power BI stores the records in order to acheive high compression rates. That is why 100MB of source data can often be loaded into a Power BI database that is around 10MB (10x compression is common).  If order of the data is important, then you need to load a key that indicates the order you want.  That is how the sort order is managed in all databases including PBI.

I never said that a question/answer thread in a public forum was "the official" clearly defined process. Here is the official documentation.  https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column

It is also strange to me that you feel so annoyed by this given there is a simple, scalable, standardised solution.  


I have watched this product develop from zero to where it is today over the last 5 years. Personally I did not use it for 2 years as I felt it was not mature enough. It was around October 2017 that I felt it was good enough to at least consider. It has had 150% additional development effort since then. 


I'm
 not saying this product is right for you - I have no way of judging that.  But you did just post to say that you walked away because of countless issues without qualifying that your assessment was 2 years old, and without providing details as to what those countless issues were/are. If there are still such "this product is unusable" issues, I would like to know what they are. I lend my weight to improving the product all the time, and if there are gaps that do not have focus, I'm more than willing to help. But if there are not countless issues that make the product unusable, I think people reading this threads should know that too. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

I don't think we're going to solve these problesm tonight/today, so allow mr to make one narrow point (to get it off my chest) and then go more general.

Specifically, about the link you provided for the official clearly defined sorting process is indeed what you say it is. But even looking at it from the vanatage point of someone has been absent from the scene for a long while, it's clear it doesn't address the main (to me) issue: how to prevent any type of sorting. While I understand the need of PBI or other databases to store, compress, slice and dice data - on occassion (not frequently maybe, but still) data is immutable, sacrosanct, etched in stone. The system should be able to do all those things to the data for its own convenience, but it should also be able to output the data exactly as it was input - no surgery allowed. I am not a DB designer and have no idea how to do that, but maybe at the time data is loaded - there should be some way of indicating a "do not touch" intent. If you could lend your weight to making that possible, that would (in my opinion) be an important contribution to the cause.

On the more general level - you may actually be right; it's entirely possible I've been away long enough and significant improvements may have been made to warrant a re-examination on my part. As I said, I was positivley disposed toward PBI and really, really wanted it to work for me. So what I would probably do is take some time, reinstall, try to do some of those things I need to do frequently and see if this time things go a little more smoothly.

In the interim, Matt, thanks for your time (and attention!). It was really informative.

It is now possible to have columns with different formatting - it was released last month. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
SaVer
Advocate I
Advocate I

This option is possible in QlickSense, but unfortunately not in Power BI.

Screenshot_QS_show_column_if.jpgLike 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, ...).

 

RBear
Advocate I
Advocate I

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. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

@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?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

@MattAllington - they need to export the data because they make notes on each row during review sessions. We've tried to explore solutions where they make the notes in another form (i.e.: sharepoint, separate excel file...) while viewing the results on the report on-screen but have been hammered with negative feedback about the experience of having to have 2 documents open and lined up (read results from row 24, make notes in row 24 in the 2nd document...). 

 

I have not explored Analyze in Excel as our users do not have Power BI Pro licenses.

 

So how do they access the reports if they don't have pro licences?  Presumably Power BI Premium.

 

Have you tried Export to PDF or PowerPoint?  There is also some commenting features in Dashboards, but I doubt that will do it for you.    I suspect there is not currently a simple solution.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Sigh... I guess my (mental) transition from Excel to PBI is not going to be as smooth as I was hopingMan Sad

 

One day I'll get it, maybe - thanks for the input.

josipinho
Helper II
Helper II

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.

 

  1. Create 2 copies of the table, one with the column and one without the column.
  2. Turn on bookmarks preview feature (file\options)
  3. Turn on the bookmarks pane and the selection pane
  4. Create 2 bookmarks.  One has one of the tables visible and the other has the other table visible
  5. 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

 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors