Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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 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])
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
54 | |
26 | |
23 | |
14 | |
11 |
User | Count |
---|---|
78 | |
65 | |
46 | |
17 | |
12 |