Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
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. 

claudiascottt_0-1654046941482.png 

claudiascottt_1-1654046971924.png

 

 

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

Hi @danextian 

 

Thank you solved 🙂 

 

Can you please mark the correct reply as the solution?










Did I answer your question? Mark my post as a solution!


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.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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?










Did I answer your question? Mark my post as a solution!


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.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
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. 

claudiascottt_0-1654046941482.png 

claudiascottt_1-1654046971924.png

 

 

Please see my other reply.










Did I answer your question? Mark my post as a solution!


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.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
danextian
Super User
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 ()
    )

 










Did I answer your question? Mark my post as a solution!


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.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Anonymous
Not applicable

Hi @danextian 

 

Thanks for your help, I'm getting this error

 

claudiascottt_0-1654045886232.png

 

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

danextian_0-1654046557535.png

 

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 

 










Did I answer your question? Mark my post as a solution!


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.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.