Using a switch statement to select a column

[sjl_74]

I am completely stumped.

I'm trying to use…

a slicer to select a single value (this works)

Then use that value with a SWITCH statement to select a column (this doesn't work)

How I've set this up...

I have a table called 'Slicer_Table'

This table has one column called 'Slicer_Options'

The column contains two twos and two values 1 & 2.

I have a second table called 'Data_Table'.

This table has two columns, called 'Column_1' and 'Column_2'.

'Column_1' contains the values A, B, C, D, E, F, G

'Column_2' contains the values H, I, J, K, L, M, N

In the dashboard I have a slicer.

Which uses 'Slicer_Table->Slicer_Options' as it's field value.

It has the style Tile & has the selection 'Single Select' 'On'.

In the dashboard I have a measure called 'Select_Column'

Associated with the 'Data_Table'.

This measure (doesn't) work by…

Taking the currently selected slicer value, and storing it in the VAR SelectedColumn

e.g.,

VAR SelectedColumn = SELECTEDVALUE(Slicer_Table[Slicer_Options])

Using 'SelectedColumn' in a SWITCH statement to return Data_Table[Column_1] or Data_Table[Column_2]

Depending on the value of 'SelectedColumn'

e.g.,

SWITCH(
    SelectedColumn,
    1, Data_Table[Column_1],
    2, Data_Table[Column_2]
)

The measure as a whole looks like:

Select_Column =
VAR SelectedColumn = SELECTEDVALUE(Slicer_Table[Slicer_Options])
RETURN
    SWITCH(
        SelectedColumn,
        1, Data_Table[Column_1],
        2, Data_Table[Column_2]
    )

However, this doesn't work.

No matter what the value of 'SelectedColumn'

Nothing is returned by the SWITCH statement.

If I replace 'SelectedColumn' with a constant value e.g.,

Select_Column =
VAR SelectedColumn = SELECTEDVALUE(Slicer_Table[Slicer_Options])
RETURN
    SWITCH(
        1,
        1, Data_Table[Column_1],
        2, Data_Table[Column_2]
    )

Then it does work -

Setting the value to 1 returns Data_Table[Column_1]

Setting the value to 2 returns Data_Table[Column_2]

as I would expect.

That it works with constant values, makes me think this should be feasible with a variable value.

& that I am doing something wrong - I'm just not sure what?

[Greg_Deckler · Accepted solution]

@sjl_74 See the below formula and attached PBIX on how to fix. What I don't understand is why you aren't getting an error in your measure formula because you are referencing a column without an aggregation and that's not really a thing generally.

Select_Column = 
VAR SelectedColumn = SELECTEDVALUE(Slicer_Table[Slicer_Options])
RETURN
    SWITCH(
        SelectedColumn,
        1, MAX('Data_Table'[Column_1]),
        2, MAX(Data_Table[Column_2])
    )

[sjl_74]

The Select_Column measure worked perfectly.
Thank you so much @Greg_Deckler !