Forum Discussion

sjl_74's avatar
sjl_74
Microsoft Employee
2 years ago
Solved

Using a switch statement to select a column

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?

 

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

     

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion

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