Forum Discussion

bhattshubham's avatar
bhattshubham
Frequent Visitor
5 years ago
Solved

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 onl...
Need Help
Tips and Tricks
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    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's avatar
Jihwan_Kim
Super User
5 years ago

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