Using a switch statement to select a column

sjl_74 — Thu, 22 Feb 2024 16:45:05 GMT

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?

Re: Using a switch statement to select a column

Greg_Deckler — Thu, 22 Feb 2024 16:46:30 GMT

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]) )

Re: Using a switch statement to select a column

sjl_74 — Tue, 12 Mar 2024 11:02:43 GMT

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

topic Re: Using a switch statement to select a column in DAX Commands and Tips

Using a switch statement to select a column

Re: Using a switch statement to select a column

Re: Using a switch statement to select a column