Forum Discussion
If statement to give value when between two numbers.
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
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 nullSample PBIX for your reference - https://drive.google.com/file/d/1vjiQ_7xdMf5LupiLWCzu-GvGy7rm2qzF/view?usp=sharing
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.
8 Replies
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 () )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 nullSample PBIX for your reference - https://drive.google.com/file/d/1vjiQ_7xdMf5LupiLWCzu-GvGy7rm2qzF/view?usp=sharing
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.
Please see my other reply.