Forum Discussion

BeWanderlustful's avatar
BeWanderlustful
New Member
8 years ago
Solved

DAX (IF) Convert String to Integer for Survey Data

Hey Everyone,

I have survey data with the following responses stored in my DB: Very Dissatisfied, Dissatisfied, Neutral, Satisfied, Very Satisfied. I need to convert them to numbers 1-5 for calculating scores.

The following formula for a calculated column doesn't throw any errors, however, it only converts "Neutral" and "Satisfied" to their corresponding number values:

Answer_NumberValue = IF(FRS_SURVEYRESULTS[AnswerText_Trimmed] = "Very Satisfied", "5", IF(FRS_SURVEYRESULTS[AnswerText_Trimmed] = "Satisfied", "4", IF(FRS_SURVEYRESULTS[AnswerText_Trimmed] = "Neutral", "3", IF(FRS_SURVEYRESULTS[AnswerText_Trimmed] = "Dissatisfied", "2", IF(FRS_SURVEYRESULTS[AnswerText_Trimmed] = "Very Dissatisfied", "1", "")))))

 

 The AnswerText column has been trimmed to ensure there are no leading or trailing spaces, but no matter what I try, I can't get it to convert my data to the values that I need.

Any help would be much appreciated!!

  • BeWanderlustful's avatar
    BeWanderlustful
    8 years ago

    Greg,

    I'm not sure if this is what you intended, but upon right-clicking an individual value (Very Satisfied) in the Power Query, I realized I could individually Replace Values. I had tried doing this previously by selecting the entire column, and it didn't work (it would not find and replace the values). This time it did work, and I was able to change "Very#(00A0)Satisfied" to "VerySatisfied", and "Very#(00A0)Dissatisfied" to "VeryDissatisfied". Also, despite the column AnswerText_Trimmed having been trimmed and cleaned, "Dissatisfied" was still in fact "Dissatisfied#(00A0)". So I replaced that without the nonbreaking space.

    With that said, the Switch formula now works perfectly. I have no idea how the column was not properly trimmed, or why the formula wouldn't work when I tried the Unicode characters "#(00A0)" in the actual formula. But it now works.

    Which of your solutions would you prefer I mark as the answer? I'm betting my IF statement would work with this now fixed, so how about the solution for replacing the values individually?

    Thanks,

    - Nick

10 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    I would use SWITCH like:

     

    Answer_NumberValue = 
SWITCH(
     FRS_SURVEYRESULTS[AnswerText_Trimmed],
     "Very Satisfied",5,
     "Satisfied",4,
     "Neutral",3,
     "Dissatisfied",2,
     "Very Dissatisfied",1
)

    Much cleaner and should be easier to troubleshoot. 

     

    I'm wondering if perhaps there is something wrong with your spaces perhaps? Did you do a clean and trim in the query editor?

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Was just about to post this! Including the additional spaces between the words. Just ran a test list through power bi via excel and had no issues with the switch statement.

    • BeWanderlustful's avatar
      BeWanderlustful
      New Member
      8 years ago

      Sadly I'm getting the exact same result with the SWITCH statement, and I did Clean and Trim the data in the Power Query...

      I also attempted a Conditional Column in Power Query, and STILL got the same result (only Satisfied and Neutral displaying their corresponding numbers)

      I have no idea what the issue is... Especially when not even "Dissatisfied" will convert - at least if that converted I would think there was some weird spacing between the words.

       

      Any other suggestions?

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        8 years ago

        First, sanity check. Create an Enter Data query and hand type your values. Save this. Add a column and paste in your SWITCH statement. Make sure it works. If it works, then we will have to look deeper.