Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX For Categorising Observations

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's avatar
    tamerj1
    3 years ago

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

1 Reply

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

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