Hi, I am completely tearing my hair out here. I have no idea why this doesn't work.
Basically, I am trying to select a column based on a selected value (which is in turn, chosen via slicer) using a switch statement. Here is my DAX code that DOES NOT WORK (Table & column names edited for anonymity):
SelectedColumn =
var quarter = SELECTEDVALUE('Date Look-Up'[Quarter])
RETURN
SWITCH(
quarter,
"Q1",'MyTable'[Column Q1],
"Q2",'MyTable'[Column Q2],
"Q3",'MyTable'[Column Q3],
"Q4",'MyTable'[Column Q4]
)
Here some of the changes I made to troubleshoot:
1) Changing the variable "quarter" to one of the values used in the SWITCH statement:
SelectedColumn =
var quarter = "Q4"
RETURN
SWITCH(
quarter,
"Q1",'MyTable'[Column Q1],
"Q2",'MyTable'[Column Q2],
"Q3",'MyTable'[Column Q3],
"Q4",'MyTable'[Column Q4]
)
Works! But, I don't want to just select 1 value, I want the selected value to change. So, no good. It proves that the switch statement CAN work.
2) Converting the SELECTEDVALUE to a string:
SelectedColumn =
var quarter = CONVERT(SELECTEDVALUE('Date Look-Up'[Quarter]),STRING)
RETURN
SWITCH(
quarter,
"Q1",'MyTable'[Column Q1],
"Q2",'MyTable'[Column Q2],
"Q3",'MyTable'[Column Q3],
"Q4",'MyTable'[Column Q4]
)
DOESN'T WORK
3) Making a test measure where it outputs a number depending on the SELECTEDVALUE:
TestMeasure =
var quarter = SELECTEDVALUE('Date Look-Up'[Quarter])
RETURN
SWITCH(
quarter,
"Q1",1,
"Q2",2,
"Q3",3,
"Q4",4
)
WORKS! But I don't need numbers, I need it to select the column. (Note, this measure also works if I replace each letter with an aggregation, for example SUM('MyTable'[Column Q1]), but I don't want an aggregation, I want the whole column.)
4) Using the TestMeasure to return a number which is then fed into the switch statement for selecting a column
TestMeasure =
var quarter = SELECTEDVALUE('Date Look-Up'[Quarter])
RETURN
SWITCH(
quarter,
"Q1",1,
"Q2",2,
"Q3",3,
"Q4",4
)
And then,
SelectedColumn =
var quarter = MyTable[TestMeasure]
RETURN
SWITCH(
quarter,
1,'MyTable'[Column Q1],
2,'MyTable'[Column Q2],
3,'MyTable'[Column Q3],
4,'MyTable'[Column Q4]
)
DOESN'T WORK
SO, selecting a column based on a switch statement works IF the variable is set to a constant.
AND, I can output different values based on the SELECTEDVALUE using a switch statement (in a measure)
BUT I can't combine these two in order to select a column with a SWITCH statement!!!
What is happening here??? I feel like I'm going completely insane!!! Help!!!
