Dynamically Add or Remove Dimension or attributes to a table visual using a slicer selection

[AbdulAzad]

Hi, I am pretty new to Power BI, I wanted to show and hide columns in a table visual based on slicer selection, columns are like Effective_Date, Purchase_Date, Quantity, Order_Number. How can I achieve this. I have seen so many videos of show/hide columns on measures not on dimension or attribute or date columns. Your help on this will be much appreciated. Thanks & Regards, A

[ricardomadaleno]

Hi, did you ever found a solution?

I am interested in doing something similar to this.

Thanks

[itsmebvk]

Please see the attached PBIX .

Follow these steps:

1) Import data in to Power BI I am using excel as source

2) Then you need to insert index column on Query 1 (if you don't have any key column)

3) Then you need to duplicate your Query1 as Query2

4) Then remove unnecessary columns from Query2 except the columns you want in slicer and Index column. In this example I have used Plant,Turbine, Index

5) Then select Plant and Turbine column in Query 2>Right Click>Unpivot

6) Now you will see new column names as Attribute and Values in Query2

7) Now use Query 2 Attribute column as slicer

😎 Add a bar chart to the report then add Value from Query2 to Bar chart

9) Add other measures or dimensions from Query1 to chart

10) Now you should be able to switch dimensions using slicer, but if you observe data is showing incorrectly on charts, this is because of Cross Filter Direction set to SIngle

 

11) You can fix this issue by changing cross filter direction to both or creating new measure using following code

Calculated Alarrm Count = CALCULATE(SUM('Query1'[Alarm_Count]),CROSSFILTER(Query2[Index],'Query1'[Index],Both))

I strongly suggest you to do it using Calculated Measure or instead of changing Join

12) Now add newly created measure to the report , it should display data as expected.

Please see the attached PBIX .

[AbdulAzad]

Hi Vamsi, Thank you very much for you reply. For my requirement this approach doesn't fit, because my data volume is huge on top of it I have to duplicate the queries and do pivot. I have replied my detailed requirement in the same post. If you have some other approach which fits my requirement that will be much appreciated. Thanks & Regards, A

[v-yuta-msft]

@AbdulAzad,

Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

Regards,

Jimmy Tao

[AbdulAzad · Accepted solution]

Hi Jimmy,

It didn't solve to the extent I expected, because I wanted even dimensions to be shown and hidden based on slicer selection, the solution provided will not work, if I try to pivot my table on huge volumes of data. Where I used the DAX expressions to hide and display only measures not the dimensions at this point of time.

Thanks & Regards,

Abdul

[v-yuta-msft]

@AbdulAzad,

Maybe this thread can help you:

https://community.powerbi.com/t5/Desktop/Dynamic-Category-Using-a-Slicer/m-p/216971#M96101

Regards,

Jimmy Tao

[v-yuta-msft]

@AbdulAzad,

I'm afraid slicer can only filter rows in visuals, the columns in chart is decided when you put the columns in field of visual. As a workaround, I would suggest you to use bookmarks instead.

Reference for bookmarks: https://docs.microsoft.com/en-us/power-bi/desktop-bookmarks.

Regards,

Jimmy Tao

[AbdulAzad]

Hi Jimmy, Thank you very much for your reply. Bookmarks doesn't work out for my requirement. My requirement is I have a table visual which has number of columns, out of which I wanted to provide user the option to show or hide few columns based on user selection in a multi select check box slicer. By default all columns are displayed as default. Based on the user selection I want to hide or show only the selected columns. I used the concept of power-pivot which doesn't suit my requirement where my data might be huge and in power-pivot I have only the option to separate measures and dimensions differently, which is not what I wanted ,I hope you understood my requirement. Thanks & Regards, A