Forum Discussion
Change Table Columns Based on Slicer Selection
Super UserHello 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()
)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.
- Sahir_Maharaj
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.
elaine1217Helper I
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!
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.
