DAX For Categorising Observations

[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, but I believe I need an aggregate function within it to calculate the totals for each/DAX to recognise the column. 

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")

If the DAX can be adjusted to include the function that would be great, thank you.

[tamerj1 · Accepted solution]

Hi @BotBot1 
If you are creating a measure then try

Observation Category Rating =
VAR ObservationCategories =
    SELECTEDVALUE ( 'HSE Observation'[CategoryTypeName] )
RETURN
    SWITCH (
        ObservationCategories,
        "Innovation", "Positive",
        "Exceeding Company Standards", "Positive",
        "Company Standard", "Netural",
        "Unknown", "Unknown",
        "Negative"
    )

If you are creating a calculated column then try

Observation Category Rating =
VAR ObservationCategories =
    'HSE Observation'[CategoryTypeName]
RETURN
    SWITCH (
        ObservationCategories,
        "Innovation", "Positive",
        "Exceeding Company Standards", "Positive",
        "Company Standard", "Netural",
        "Unknown", "Unknown",
        "Negative"
    )