Dynamic Columns in table visual based on slicer selection

[bhattshubham]

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

[v-yalanwu-msft · Accepted solution]

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.

[Jihwan_Kim]

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