DAX to show exceptions to formula

[Anonymous]

I have a table that has a column showing the risk level of risks. This risk level is based on a risk score in another column. However there are two risk levels that are exceptions to this approach.

The risk level column uses a formula to show the level, which works for all but two of the risk scores. I have been unable to come up with a variation to the formula that enables these two exceptions to show correctly.

Refer this file: 

https://www.dropbox.com/s/nqe68ofukhmhpqp/Example7.pbix?dl=0

Any help appreciated.

Steve

[Zubair_Muhammad]

@Anonymous

You can use a SWITCH statement to define a different rule/formula for Risks 11 and 15

For example

Risk Level =
SWITCH (
    TRUE (),
    [Risk Number] IN { 11, 15 }, "Define your rule here",
    IF (
        [Risk Score] >= 15,
        "High",
        IF (
            AND ( [Risk Score] < 15, [Risk Score] >= 6 ),
            "Medium",
            IF ( [Risk Score] <= 5, "Low" )
        )
    )
)

[Anonymous]

Thank you for the response. The proposed solution would certainly work with the data provided. However, this data is updated on a regular basis and new risks are added. So I would need a formula that enables the two exceptions (1. the risk level is Low if the Consequence Score = 3 and Likelihood Score = 2, and 2. the risk level is Medium if the Consequence Score = 5 and Likelihood Score = 1) to be considered each time a new risk is added.

Does this make sense?

Steve

[Zubair_Muhammad · Accepted solution]

@Anonymous

In that case, you can use

Risk_Level =
VAR Condition1 =
    AND ( [Consequence Score] = 3, [Likelihood Score] = 2 )
VAR Condition2 =
    AND ( [Consequence Score] = 5, [Likelihood Score] = 1 )
RETURN
    SWITCH (
        TRUE (),
        Condition1, "Low",
        Condition2, "Medium",
        IF (
            [Risk Score] >= 15,
            "High",
            IF (
                AND ( [Risk Score] < 15, [Risk Score] >= 6 ),
                "Medium",
                IF ( [Risk Score] <= 5, "Low" )
            )
        )
    )