Parameter: Fields selected value

[rdg515]

I want to create a measure that references the selected value from a paramater table (create new parameter, select fields). I will show you the way I did it below, but I'm looking for an improvement because if I change the name of the parameter table I have to update all the hardcoded references to it.

Here's is the parameter table:

prmSurveyResponses = { ("NPS", NAMEOF('Service Survey'[NPSResponseCode]), 0), ("Customer Effort", NAMEOF('Service Survey'[CustomerEffortResponseCodeText]), 1), ("Product vs Competition", NAMEOF('Service Survey'[ProductCompetitionResponseCodeText]), 2) 

Then I created a slicer of prmSurveyResponses[prmSurveyResponses] which represents measures that the user can select to see.

Finally, I created a measure that will dynamically select the appropriate measure based on the user's selection from the slicer. 

Selected Survey Avg Rating = SWITCH(SELECTEDVALUE(prmSurveyResponses[prmSurveyResponses Fields]), "'Service Survey'[NPSResponseCode]",[NPS Avg Rating], "'Service Survey'[CustomerEffortResponseCodeText]",[Customer Effort Avg Rating], "'Service Survey'[ProductCompetitionResponseCodeText]",[Product Competition Avg Rating] )

I cannot figure out how to do this without hardcoding the table name - anyone know how to avoid having to do this?

[v-kkf-msft · Accepted solution]

Hi @rdg515 ，

Please try the measure.

Selected Survey Avg Rating = 
SWITCH (
    SELECTEDVALUE ( prmSurveyResponses[prmSurveyResponses Fields] ),
    NAMEOF ( 'Service Survey'[NPSResponseCode] ), [NPS Avg Rating],
    NAMEOF ( 'Service Survey'[CustomerEffortResponseCodeText] ), [Customer Effort Avg Rating],
    NAMEOF ( 'Service Survey'[ProductCompetitionResponseCodeText] ), [Product Competition Avg Rating]
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rdg515 · Accepted solution]

Thanks, but I was looking for a way to avoid referencing the parameter table in the Avg Rating measure because I had many measures like this that I had to update when I changed the name of the parameter table.  Having said that, I realized I missed an obvious solution which was to just do this in two steps and if I had to change the paramater table name again I'd just have to do it for one measure.

Step 1:

Selected Parameter = SELECTEDVALUE ( prmSurveyResponses[prmSurveyResponses Fields] )

Step 2:

Selected Survey Avg Rating =
SWITCH (
    [Selected Parameter],
    NAMEOF ( 'Service Survey'[NPSResponseCode] ), [NPS Avg Rating],
    NAMEOF ( 'Service Survey'[CustomerEffortResponseCodeText] ), [Customer Effort Avg Rating],
    NAMEOF ( 'Service Survey'[ProductCompetitionResponseCodeText] ), [Product Competition Avg Rating]
)

Thanks for the tip to use NAMEOF instead of using double quotes around the column name.

P.S. I keep getting the following error when formatting the dax code as HTML/XML code snippit:

Your post has been changed because invalid HTML was found in the message body. The invalid HTML has been removed. Please review the message and submit the message when you are satisfied.

[Mohith_ds]

The protip to use NAMEOF was a lifesaver. Thanks.

[v-kkf-msft · Accepted solution]

Hi @rdg515 ，

Please try the measure.

Selected Survey Avg Rating = 
SWITCH (
    SELECTEDVALUE ( prmSurveyResponses[prmSurveyResponses Fields] ),
    NAMEOF ( 'Service Survey'[NPSResponseCode] ), [NPS Avg Rating],
    NAMEOF ( 'Service Survey'[CustomerEffortResponseCodeText] ), [Customer Effort Avg Rating],
    NAMEOF ( 'Service Survey'[ProductCompetitionResponseCodeText] ), [Product Competition Avg Rating]
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Here is a more universal solution. 

Selected Parameter = 
VAR SearchString = SELECTEDVALUE('Field Parameters'[Parameter])
VAR LeftBound = SEARCH("[",SearchString)
VAR RightBound = SEARCH("]",SearchString)
RETURN
MID(SearchString,LeftBound + 1,RightBound-(LeftBound+1))