Forum Discussion
Dynamic Columns in table visual based on slicer selection
Hi
I am looking to create a table in PowerBI desktop which should dynamically show columns based on the slicer. i.e If in the Commodity Slicer "Com 1" is selected, the columns corresponding to only Com 1 should show in the table (Com 1 - Attribute 1, Com 1 - Attribute 2). And if "Com 2" is selected in the slicer then only columns corresponding to Com 2 should show in the table ( Com 2 - Attribute 1, Com 2 - Attribute 2, Com 2 - Attribute 3). Please help with suggestions. Below is sample data.
| S No. | Commodity | Com 1 - Attribute 1 | Com 1 - Attribute 2 | Com 2 - Attribute 1 | Com 2 - Attribute 2 | Com 2 - Attribute 3 |
| 1 | Com 1 | xx | xx | (Blank) | (Blank) | (Blank) |
| 2 | Com 1 | xx | xx | (Blank) | (Blank) | (Blank) |
| 3 | Com 1 | xx | xx | (Blank) | (Blank) | (Blank) |
| 4 | Com 2 | (Blank) | (Blank) | xx | xx | xx |
| 5 | Com 2 | (Blank) | (Blank) | xx | xx | xx |
| 6 | Com 2 | (Blank) | (Blank) | xx | xx | xx |
| 7 | Com 2 | (Blank) | (Blank) | xx | xx | xx |
| 8 | Com 2 | (Blank) | (Blank) | xx | xx | xx |
Hi bhattshubham ,
Currently, slicer cannot based on columns name. To work around this issue, you could Unpivot your table. Please refer to the steps below:
1.Unpivot table(select Column “Com 1 - Attribute 1” to “Com 2 - Attribute 3”>Unpivot Columns)
2.Remove empty
3.Result
4.Now you can create a matrix and filter your data in slicer by Commodity column.
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi, bhattshubham
I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.
I simplified it a little bit but I hope you can get the concept.
Sales by AxisSlicer =
SWITCH (
SELECTEDVALUE ( SlicerTable[Slicer Index] ),
1,
CALCULATE (
SUM ( Sales[Qty] ),
TREATAS ( VALUES ( SlicerTable[Axis] ), Dates[DateAxis] )
),
2,
CALCULATE (
SUM ( Sales[Qty] ),
TREATAS ( VALUES ( SlicerTable[Axis] ), Dates[Week & Year] )
),
3,
CALCULATE (
SUM ( Sales[Qty] ),
TREATAS ( VALUES ( SlicerTable[Axis] ), Dates[Month & Year] )
)
)https://www.dropbox.com/s/2t7vxnt9b7cc27s/bhat.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
Hi bhattshubham ,
Currently, slicer cannot based on columns name. To work around this issue, you could Unpivot your table. Please refer to the steps below:
1.Unpivot table(select Column “Com 1 - Attribute 1” to “Com 2 - Attribute 3”>Unpivot Columns)
2.Remove empty
3.Result
4.Now you can create a matrix and filter your data in slicer by Commodity column.
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
