Forum Discussion
Replace continuous numerical data with categorical text data
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.
Anonymous 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" )Hi Anonymous ,
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.
5 Replies
Anonymous 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" )Hi Anonymous ,
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.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/
