Forum Discussion
Change Table Columns Based on Slicer Selection
Hi All, I'm looking for a way to switch out columns in a table if a selection from a slicer is made. Example Year Slicer = 2021, show Columns A, B and C in my table or if Year Slicer = 2022, s...
MohamedFowzan1
Super User
Super UserI understand there is no straight forward way to do this. I was able to achieve this without having to use bookmarks.
Here’s how I solved this:
- Unpivot your columns (A, B, C, etc.) in Power Query to get a “long” format
- Create a mapping table that specifies which columns (attributes) appear for each year, for example:
- Create a YearSelection slicer table (distinct years).
- Set up relationships so filtering flows like this:
- YearSelection → YearColumnMapping → Columns Lookup → Fact Table
- Build your matrix visual with:
- Rows: USER
- Columns: Attribute
- Values: Value
- When you select a year, only the corresponding columns appear in the matrix.
This method is fully dynamic, scalable, and avoids bookmarks or multiple stacked tables.
I have the PBIX of the model and visual so you can see exactly how the relationships and slicer interact but not able to attach it here.
Hello, could you please elaborate the below part in your solution?
YearColumnMapping → Columns Lookup → Fact Table
What's the steps in 'Columns Lookup againt Fact Table'? Thanks a lot.
- MohamedFowzan1
Posting screenshots of Paramters, dataset and DAX for better understanding:
Ref 1:
Ref2:
Ref3:
Ref4:
Ref5:
Actual table:
Ref6:
Unpivot table:
Ref7:
Relationships:
This would be a starting point for you to play around with actual data and build on it.