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
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
Solved! Go to 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.
Can you please mark the correct reply as the solution?
Proud to be a 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!
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.
Proud to be a 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!
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
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
105 | |
105 | |
88 | |
61 |
User | Count |
---|---|
165 | |
133 | |
132 | |
95 | |
86 |