topic Selecting a column from a selected value in DAX Commands and Tips

Selecting a column from a selected value

rwalker — Wed, 31 Jul 2024 15:37:14 GMT

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!!!

Re: Selecting a column from a selected value

DataInsights — Wed, 31 Jul 2024 16:20:36 GMT

rwalker,

Are you trying to dynamically select a column to display in a visual? If so, field parameters are a great option:

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

Or, are you trying to dynamically select a column's value for further use in your measure? It's worth noting that a calculated column can't recognize a slicer selection, so a measure is the only option.

Re: Selecting a column from a selected value

rwalker — Wed, 31 Jul 2024 16:46:45 GMT

I am trying to dynamically select a column to display in a visual. I clicked your link but for me there is no "Preview Features" in the options menu, so I can't do it.

Is there a way to do this with a measure instead of a calculated column? I want all the rows to display in a visual, not an aggregation.

Re: Selecting a column from a selected value

DataInsights — Wed, 31 Jul 2024 19:42:11 GMT

rwalker,

What version of Power BI Desktop do you have? If you don't see "Field parameters" below, upgrade Power BI Desktop:

Re: Selecting a column from a selected value

RameezM1984 — Sat, 09 Nov 2024 05:19:59 GMT

Hello

Did u get the solution for the above?

I am also facing the same problem..if you have a solution ..kindly share