Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
Anonymous
Not applicable

@MattAllington 

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. 



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

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”



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

@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
Microsoft Employee
Microsoft Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

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. 



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

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

 

sample data.png

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 



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

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.

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

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

MattAllington
Community Champion
Community Champion

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



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

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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
Not applicable

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

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

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.