If statement to give value when between two numbers.

[Anonymous]

Hi everyone, 

Can someone help me come up with a formula to give a categorised value for between numbers. 

Rating Ranges: 1-5, 6-10, 11-15, 16-20, 21-25
Categories: Insignificant, Minimal, Moderate, Significant, Extreme 

Im hoping to come up with something like IF [Rating] between(1,5) then "Insignificant" etc

[danextian · Accepted solution]

That formula is to be done in the Designer and not in Power Query.

If you want it done in Power Query (which has a different syntax and is a separate language) try this.

let 
VAL = [Inherent Risk Rating]
in
if 
VAL <= 5 then "Insignificant" else if
VAL <= 10 then "Minimal" else if
VAL <= 15 then "Moderate" else if
VAL <= 20 then "Significant" else if
VAL <= 25 then "Extreme" else 
null

Sample PBIX for your reference - https://drive.google.com/file/d/1vjiQ_7xdMf5LupiLWCzu-GvGy7rm2qzF/view?usp=sharing 

[Anonymous]

Hi @danextian 

Thank you solved 🙂 

[danextian]

Can you please mark the correct reply as the solution?

[danextian]

Hi @Anonymous ,

That happens when you're comparing a number against a text  (or vice versa( or using < operator against a text.  What formula did you input?  Have you checked on the PBIX in my other comment?

[Anonymous · Accepted solution]

Sorry I'm not sure I've given you enought context. This formula is to look at another column, "Inherent Risk Rating" that is currently a numerical value which I want to group into the categories mentioned above. What I'm trying to do is add a custon column in power query that takes the Inherent Risk Rating and it groups it depending on what range it is in. 

 

[danextian]

Please see my other reply.

[danextian]

Hi @Anonymous ,

Try this as a calculated column:

Category = 
VAR __VAL = 'Table'[Value]
RETURN
    SWITCH (
        TRUE (),
        __VAL <= 5, "Insignificant",
        __VAL <= 10, "Minimal",
        __VAL <= 15, "Moderate",
        __VAL <= 20, "Significant",
        __VAL <= 25, "Extreme",
        BLANK ()
    )

[Anonymous]

Hi @danextian 

Thanks for your help, I'm getting this error

[danextian · Accepted solution]

That formula is to be done in the Designer and not in Power Query.

If you want it done in Power Query (which has a different syntax and is a separate language) try this.

let 
VAL = [Inherent Risk Rating]
in
if 
VAL <= 5 then "Insignificant" else if
VAL <= 10 then "Minimal" else if
VAL <= 15 then "Moderate" else if
VAL <= 20 then "Significant" else if
VAL <= 25 then "Extreme" else 
null

Sample PBIX for your reference - https://drive.google.com/file/d/1vjiQ_7xdMf5LupiLWCzu-GvGy7rm2qzF/view?usp=sharing