Dynamically adding and removing columns on a table visual

[gpaltao]

Our organization have plans of transitioning certain reports from Spotfire to PowerBI but we are encountering some difficulties in replicating some functionalites we have in Spotfire, particularly dynamic addition and removal of columns in a table visual. In spotfire through the combination of html, javascript and ironpython, users are able to freely choose the category and value column they want and the table automatically readjusts the data based on the categories and values selected (autosums and autogroups data based on whichever categories are selected). But i am unable to do this in PowerBI.

I have tried to search and most of what I found entails unpivoting the data which is not practical for us as the dataset is too big (both columns and rows). The closest thing I found is from this https://community.powerbi.com/t5/Community-Blog/Dynamic-Hideable-Columns-in-Power-BI-Table-Visual/ba.... But the issue with the said solution is that certain columns that serves as primary keys needs to be always selected in the visual in order to get accurate values and because of this auto adjustment of data when other categories are selected is not working. Column headers are an issue too as there is no simple way to have them in sync with column data.

Is what we are requiring possible in PowerBI?

[ctmullins]

Any update on this topic?  I DESPERATELY need some dynamic show/hide column functionality that is friendly for my users - the "Personalize" approach works, but isn't exactly intuitive for my audience.

Coming from a Qlik world, this was trivially easy to implement, which we did often, with good results.  Surely the brains within Microsoft can build this in...?

[MFelix]

Hi @ctmullins,

Now there's is an option of field parameter that allow you to create a table with the fields you want or measures and the use a slicer to add or remove it from the visualizations.

https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

[gpaltao]

Hi, 

   Just giving a follow up on my question. A colleague pointed out the personalize visuals functionality and that is what we ended up applying to our report. Checking now, I see that MFelix suggested the same and I am marking his answer as the solution.

  Thank you guys.

[PaulDBrown]

Apologies for bringing this topic up again, but I came across this which might be pertinent to the request:

https://www.havensconsulting.net/blog-and-media/conditionally-show/hide-matrix-columns 

[PaulDBrown]

@gpaltao 

I would say the solution provided by @MFelix is probably the best option, offering great flexibily. However it might be worth checking this out (please excuse the shameless plug..) if the selection is to be constrained:

https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1... 

[MFelix · Accepted solution]

Hi @gpaltao ,

Believe that the option you need is to allow users to personalize visuals in a report:

https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-personalize-visuals

With this option active you can have user make changes on the visualization of:

• Change the visualization type
• Swap out a measure or dimension
• Add or remove a legend
• Compare two or more measures
• Change aggregations, etc.

This can be active at a single visualization level, at a page level or at a full report level.