If Statement question

[Clint]

Hello,

I'm trying to write an If (or Switch - neither is working) statement that returns a string of "low" "moderate" or "high" based on a risk exposure value.  I've tried something like this:

C_Risk Severity =

If('risks'[exposure]>1,"Low",

If('risks'[exposure]<=2, "Low",

If('risks'[exposure]>2, "Moderate",

If('risks'[exposure]<=4, "Moderate",

iF('risks'[exposure]>4, "High"

)))))

But the calc just returns low when it should return "moderate" (per the embedded image)

Exposure is a calculated field: Impact 1-5 and probablity 0-100%.  So the highest exposure is a 5 and the lowest is 0.  The calc column doesn't return any errors, it just doesn't return a string of "moderate" based on the score of a 3.

I did try this as a Switch as well and achieved the same results.

[alexvc · Accepted solution]

Hi there,

The problem is the order of the conditions, for instance, if you evaluate a 5 in your formula the first condition is if > 1 then Low. This is the first condition that runs into and complies, so the result will be low instead of high. Also, there are more conditions that needed, I recommend you rewrite your formula starting from the lowest conditional value up to the highest, and only using one way condition (either < or >):

C_Risk Severity =

If('risks'[exposure]<=2, "Low",If('risks'[exposure]<=4, "Moderate","High"))

Let me know if this helps

[Clint]

Thank you @alexvc ,  this seems to have worked just fine.  I wrote the initial statement the way I did thinking it would allow me to tell BI to parse between values and return the correct string i.e, if the value is greater than 2 but less than 4, return moderate etc...  I tried using <=2>4 but this generates error about using boolean to compare true/false.  Is there away to parse ranges between values to return a string?  If statements could get clunky if the calc gets too complex.

[alexvc]

To state those kind of conditions you must use AND or OR. In the case you mention the condition should look something like IF(AND('risks'[exposure]>2,'risks'[exposure]<4),"Moderate",""). The problem with this is that it will only consider that "in between" range when evaluating a value

In my experience, IF nested statements (like this case) work fine, you just need to follow the logic behind it. IF formulas evaluate values following conditions from left to right, so if you evaluate the value 3 in the formula C_Risk Severity =

If('risks'[exposure]<=2, "Low",If('risks'[exposure]<=4, "Moderate","High")), the fist evaluation is 3<=2. This returns FALSE, then it goes for the next, 3<=4. This returns TRUE (returning "Moderate"), so if you think about it is really doing what you mention, determining if its greater than 2 but less than 4

[Clint]

That makes sense.  Definitely will be more thoughtful when thinking thru the logic of a nested IF.  Thank you!