Forum Discussion
Dynamically Add or Remove Dimension or attributes to a table visual using a slicer selection
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
8 Replies
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
- 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
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
8) 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 Join12) Now add newly created measure to the report , it should display data as expected.
Please see the attached PBIX .
- 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
Maybe this thread can help you:
https://community.powerbi.com/t5/Desktop/Dynamic-Category-Using-a-Slicer/m-p/216971#M96101
Regards,
Jimmy Tao
