topic Re: Change Table Columns Based on Slicer Selection in DAX Commands and Tips

Change Table Columns Based on Slicer Selection

stsmith67 — Thu, 02 Mar 2023 18:53:38 GMT

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, show Columns A, B, D, G or if

Year Slicer = 2023, show Columns A, D, E, etc,

Has anyone found a way to do this while trying to use the same table and not have to resort to bookmarks using stacked tables? Thanks.

Re: Change Table Columns Based on Slicer Selection

Sahir_Maharaj — Sat, 04 Mar 2023 22:07:03 GMT

Hello stsmith67,

You can achieve this using the "Switch" function in DAX:

Selected Columns = SWITCH ( SELECTEDVALUE ( 'Year'[Year] ), 2021, SELECTCOLUMNS ( Table, "A", [A], "B", [B], "C", [C] ), 2022, SELECTCOLUMNS ( Table, "A", [A], "B", [B], "D", [D], "G", [G] ), 2023, SELECTCOLUMNS ( Table, "A", [A], "D", [D], "E", [E] ), BLANK() )

Re: Change Table Columns Based on Slicer Selection

Sahir_Maharaj — Sat, 04 Mar 2023 22:08:04 GMT

The "Switch" function checks the selected value of the slicer and returns the appropriate set of columns using the "SelectColumns" function. If no value is selected in the slicer, the formula returns BLANK().

You can then use the "Selected Columns" measure in your table visualization instead of selecting the columns directly. When you change the value in the slicer, the columns displayed in the table will update automatically based on the selected year.

Let me know if you might need further guidance.

Re: Change Table Columns Based on Slicer Selection

stsmith67 — Mon, 06 Mar 2023 13:03:11 GMT

I tried that and it looked like it was going to work, but then I get this error message.

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Re: Change Table Columns Based on Slicer Selection

Anonymous — Fri, 31 Mar 2023 22:45:14 GMT

Is there a way to change within the select statement? For example, select if 1=1 then column A vs B , column 2, if x=y then C vs D, column 4???

Re: Change Table Columns Based on Slicer Selection

elaine1217 — Mon, 10 Apr 2023 13:51:23 GMT

Hi,

I also am trying to do this and got the same error as above about the multiple values not being converted to scalar value. Your response explained how it works but didn't really answer why this error would occur. Do you know why we would be getting this error?

Thanks!

Re: Change Table Columns Based on Slicer Selection

Sultanista — Fri, 15 Mar 2024 08:31:22 GMT

Try this one
https://www.youtube.com/watch?v=CHGmuzxu_BE (in the description there is a link to 1st video that might be needed to continue with this one)

Re: Change Table Columns Based on Slicer Selection

sharmil_28 — Tue, 22 Jul 2025 12:08:49 GMT

Sahir_Maharaj
I tried but measure giving the following error.
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Re: Change Table Columns Based on Slicer Selection

MohamedFowzan1 — Thu, 24 Jul 2025 11:45:52 GMT

I 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.

Re: Change Table Columns Based on Slicer Selection

MoonlightDaddy — Fri, 25 Jul 2025 20:07:14 GMT

Hello, has this been resolved?

Re: Change Table Columns Based on Slicer Selection

MoonlightDaddy — Fri, 25 Jul 2025 20:09:57 GMT

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.

Re: Change Table Columns Based on Slicer Selection

MohamedFowzan1 — Sat, 26 Jul 2025 01:35:44 GMT

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.

Re: Change Table Columns Based on Slicer Selection

MohamedFowzan1 — Sun, 27 Jul 2025 04:49:46 GMT

stsmith67 MoonlightDaddy Did the screenshots and explanation solve the issue?