Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sjl_74
Employee
Employee

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.

sjl_74_0-1708616923650.png

 

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

sjl_74_1-1708616939721.png

 

In the dashboard I have a slicer.

sjl_74_2-1708617486206.png


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

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

sjl_74_3-1708617504892.png

 

In the dashboard I have a measure called 'Select_Column'

Associated with the 'Data_Table'.

sjl_74_4-1708617530074.png

 

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:

 

sjl_74_6-1708617627688.png

 

 

 

 

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.

sjl_74_7-1708617694699.pngsjl_74_8-1708617765370.png

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?

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.