Categorising Observations into Ratings

[BotBot1]

Hi,

I have observations that need to be categorised into their rating groups, being "Positive", "Neutral", "Negative" and "Unknown" based on some of the descriptions within the variable. 

I need "Innovation" and "Exceeding company standards" to be in Positive, "Company standard" to be Neutral, "Unknown" to be Unknown, and the rest to be in Negative. This is coming from the variable [CategoryTypeName] within the "HSE Observation" field. I have attempted at writing the DAX for this but if I could receieve any help for where I went wrong it would be hugely appreciated.

Observation Category Rating =

VAR

    ObservationCategories = FILTER(ALL('HSE Observation'[CategoryTypeName])

RETURN

    SWITCH(TRUE(), ObservationCategories = "Innovation"||"Exceeding Company Standards", "Positive", ObservationCategories = "Company Standard", "Netural", ObservationCategories = "Unknown", "Unknown", "Negative")

I cannot use Data Groups or Power Query so has to be a DAX. Thank you.

[tamerj1]

Hi @BotBot1 

in which table are creating this column? What is the relationship between this table and 'HSE Observation'?

[BotBot1]

Hi @tamerj1,

I'm not too sure on this, but I am connected to a live dataset (SQL) whereby HSE Observation is the field and CategoryTypeName is one of the variables within the field. I have a measure of 'count of observations' which is where I want to get the number of positive/neutral/negative/unknown ratings from. I currently have a pie chart where it breaks it down the approx. 15 different category names (e.g. company standard, innovation...), but I just want to categorise these into the 4 ratings. I hope this helps, thanks 

[FreemanZ]

hi @BotBot1 

Try like:

Rating =

SWITCH(

    TRUE(),

    [CategoryTypeName] IN {"Innovation", "Exceeding Company Standards"}, 

   "Positive",

   CategoryTypeName] = "Company Standard", 

   "Netural",

    [CategoryTypeName] = "Unknown", 

   "Unknown", "Negative"

)

[BotBot1]

Hi @FreemanZ, 

Thanks for this. I recieve the below message when inputting this. I am not sure how to but if I could have a COUNT be added to get the total number per new category that would be great, thank you.