Hide/Show Column in Table Visual Matrix on Report

[anandsoftweb]

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,

[Anonymous]

@MattAllington 

Hi Matt,

In a matrix visual, is there a way to dynamically hide blank columns?  Thanks!

Best regards,

Ferdinand

[MattAllington]

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. 

[MattAllington]

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”

[josipinho]

@MattAllington

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.

[v-yulgu-msft]

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 Gu

[anandsoftweb]

@v-yulgu-msft

I 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.

[MattAllington]

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. 

[anandsoftweb]

@MattAllington

Below is the sample data... Now I want some filter or slicer (mechanism) that can hide value column from report by user interation 

if we can not hide Value column then something like Value column data should be hide that can work as well 

I hope now you can understand.

[MattAllington]

You cannot show and hide a column in Power BI.  One option is you could hide or show the values in the column without actually hiding the column.  Example attached

I have created a disconnected table that is then used in an if statement.  Take a look here

https://www.dropbox.com/sh/b4mug9j96xp0j7e/AABo0fXptDaQT2ifBbBggYyfa?dl=1 

[Phil_Seamark]

How about this for an approach.

https://wgtnpowerbi-my.sharepoint.com/personal/phil_wgtnpowerbi_onmicrosoft_com/_layouts/15/guestacc...

It requires some pivoting and pre-processing which can be done easily enough, But you get the idea and shows how you can show/hide columns including the header.

[Anonymous]

It looks link doesn't works, can you please recheck once and correct the link?

[MattAllington]

I don't believe this is currently possible. You can probably hide the results (e.g. Values) but not the column headings. 

[Phil_Seamark]

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.

[aarshps]

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.

[Anonymous]

I have exactly the same problem, no updates on this?

It would be just an option to hide a column when BLANK(), but it is not available for Columns, only for Rows...

Regards,

Antonio

[anandsoftweb]

@Phil_Seamark

Well pivot of data is not possible right now,

If you can suggest some way using DAX that would be really helpful.

Thanks,

[anandsoftweb]

@MattAllington

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.