Replace continuous numerical data with categorical text data

[LarkMae]

Hi, I am having difficulty finding a dax equation that can help me categorize numerical data.

I have a column in powerbi called satisfaction, inside a table called mobile. Satisfaction scores range from 0 -100. 

I would like:

0 - 29 -> Very dissatisfied

30 - 49 -> Dissatisfied

50 - 69 -> Neutral

70 - 79 -> Satisfied

80 - 100 -> Very Satisfied

I know i need to make a new column and use a DAX equation. But every equation I have tried has not worked.

[Greg_Deckler · Accepted solution]

@LarkMae Try:

I cant get no =
  VAR __Satisfaction = 'Mobile'[Satisfaction]
RETURN
  SWITCH(TRUE(),
    __Satisfaction <= 29, "Very dissatisfied",
    __Satisfaction >= 30 && __Satisfaction <= 49, "Dissatisfied",
    __Satisfaction >= 50 && __Satisfaction <= 69, "Neutral",
    __Satisfaction >= 70 && __Satisfaction <= 79, "Satisfied",
    "Very Satisfied"
  )

[Anonymous]

Hi @LarkMae ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.😀

Best Regards,
Eyelyn Qin

[Anonymous · Accepted solution]

Hi @LarkMae ,

Please follow @Greg_Deckler 's suggestion which use DAX.

Or you could go to Power Query Editor-->Add Column-->Custom Column, then paste the following M syntax:

if [Satisfaction]>=0 and [Satisfaction]<=29 then "Very dissatisfied" else if [Satisfaction]>=30 and [Satisfaction]<=49 then "Dissatisfied" else if [Satisfaction]>=50 and [Satisfaction]<=69 then "Neutral" else if [Satisfaction]>=70 and [Satisfaction]<=79 then "Satisfied" else "Very Satisfied"

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LarkMae]

This is perfect!!  

Thank you so much!!

[AlexisOlson]

You can also control this via a configuration table rather than hardcoding all the values in a DAX expression.

See this article on the static segmentation:
https://www.daxpatterns.com/static-segmentation/

[Greg_Deckler · Accepted solution]

@LarkMae Try:

I cant get no =
  VAR __Satisfaction = 'Mobile'[Satisfaction]
RETURN
  SWITCH(TRUE(),
    __Satisfaction <= 29, "Very dissatisfied",
    __Satisfaction >= 30 && __Satisfaction <= 49, "Dissatisfied",
    __Satisfaction >= 50 && __Satisfaction <= 69, "Neutral",
    __Satisfaction >= 70 && __Satisfaction <= 79, "Satisfied",
    "Very Satisfied"
  )