Calculated Column with nesting IF, THEN statements

[kbraga]

I think this is real simple but I can't seem to figure it out. I have one column with a list of text values and based on those values, I want a certin numeric value to appear in a new calculated column. Below is an example: 

Likert Scale	Associated Value
Strongly Disagree	1
Disagree	2
Neutral	3
Agree	4
Strongly Agree	5
NA	6

What is the DAX formula to get the associated value to appear based on the text in the likert scale column? 

[kbraga · Accepted solution]

Found a solution:

Likert Sort Value =

SWITCH (

TRUE (),

'Survey Response'[Text] = "Strongly Disagree", "1",

'Survey Response'[Text] = "Disagree", "2",

'Survey Response'[Text] = "Neutral", "3",

'Survey Response'[Text] = "Agree", "4",

'Survey Response'[Text] = "Strongly Agree", "5",

'Survey Response'[Text] = "NA", "6",

"0"

)

[kentyler]

Evaluates an expression against a list of values and returns one of multiple possible result expressions.

Syntax

DAXCopy

SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])  

Parameters

PARAMETERS

Term	Definition
expression	Any DAX expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context).
value	A constant value to be matched with the results of expression.
result	Any scalar expression to be evaluated if the results of expression match the corresponding value.
else	Any scalar expression to be evaluated if the result of expression doesn't match any of the value arguments.

Return value

But you might also consider creating a dimension table that had the text values in one column and the numeric values in the other. If you relate the dimension table to your fact table you will get direct access to the numeric value thru the relationship.

[az38]

Hi @kbraga 

If I understand you correct try SWITCH, like

Associated Value = 
SWITCH(Table[Likert Scale],
"Strongly Disagree", 1,
"Disagree", 2,
"Neutral", 3,
"Agree", 4,
"Strongly Agree", 5,
6
)