Forum Discussion

kbraga's avatar
kbraga
Helper I
6 years ago
Solved

Calculated Column with nesting IF, THEN statements

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 ScaleAssociated Value 
Strongly Disagree

1

Disagree

2
Neutral3
Agree4
Strongly Agree5
NA6

 

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

Need Help
Tips and Tricks
  • kbraga's avatar
    kbraga
    6 years ago

    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"
    )

3 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    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
)
  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

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

    Syntax

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

    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.

  • kbraga's avatar
    kbraga
    Helper I
    6 years ago

    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"
    )