cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## 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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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 8
Anonymous
Not applicable

Thank you solved 🙂

Super User

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Super User

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?

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Anonymous
Not applicable

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.

Super User

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Super User

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 ()
)``````

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Anonymous
Not applicable

Thanks for your help, I'm getting this error

Super User

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``````

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors