Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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" ) ) ) )
@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" ) ) ) )
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
@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" ) ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |