Changing text column into specific numbers.

[DGID]

Hello 

I am trying to make a key from 2 columns of responses. The results are either Happy, Neutral, or Unhappy and there are only two columns with those responses. I want to make a new column that has the total "score" where:

Happy = 1

Neutral = 0

Unhappy = -1 

I've tried sumx with switch nested inside to try and get this to work but have not had any luck.

To clarify in the data the columns are the 1st and 2nd Interview Outcome in the HRSYST1 table.

PBIX File

Copy of Data

Thank you in advance

[DGID · Accepted solution]

I ended up creating 6 columns for each value i.e. one for the 1st  outcome happy, one for the 2nd outcome happy etc and then adding those together to get my total column.

I feel like there has to be a more efficient way but that was all I could come up with.

Thank anyone for any insight.