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
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
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?
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.
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.
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.
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, ...).
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.
@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?
@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.
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.
Sigh... I guess my (mental) transition from Excel to PBI is not going to be as smooth as I was hoping
One day I'll get it, maybe - thanks for the input.
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
you can create another measure that is used only for hiding stuf, so you want it to always display blanks, lets call it Hide
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
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 🙂
@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.
Watch this video from Patrick at GuyInACube for a demo https://www.youtube.com/watch?v=_Afcj8mT5_Q
Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.
Watch Session 24 of Ted's Dev Camp along with past sessions!
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.