Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I have a property table of about 1.5 million rows. Each row has a risk score based wehter a dangerios material (Asbestos) has been located or not. The table contains the following columns - Property ref, Location, current status and Risk Score.
I would like a DAX function that checks the column -Risk Score and if this number is between a certain value it give me a Risk Category in a new column
Property Ref | Location | Current Status | Risk Score | Risk Category (Expected results with Dax) |
26171 | Room 1 | No Asbestos Detected | 0 | No Asbestos |
26171 | Room 1 | No Asbestos Detected | 22 | High |
26171 | Room 3 | No Asbestos Detected | 0 | No Asbestos |
26171 | Hallway | Asbestos Confirmed | 13 | Medium |
26171 | Kitchen | No Asbestos Detected | 0 | No Asbestos |
26171 | Bathroom 1 | Asbestos Confirmed | 12 | Medium |
26171 | Bathroom 2 | Asbestos Confirmed | 12 | Medium |
26171 | Loft | No Asbestos Detected | 0 | No Asbestos |
26171 | Cellar | Asbestos Confirmed | 14 | Medium |
26171 | Roof | No Asbestos Detected | 0 | No Asbestos |
26171 | Walls 1 | No Asbestos Detected | 1 | Low |
26171 | Walls2 | No Asbestos Detected | 2 | Low |
This is the Criteria I have
Order | Min | Max | Risk Category |
1 | 0 | 0 | No Asbestos |
2 | 1 | 8 | Low |
3 | 9 | 15 | Medium |
4 | 16 | 24 | High |
thank you
Richard
Solved! Go to Solution.
Hi Richard,
You might try this:
Risk Category =
var risk_score=[Risk Score]
return
SWITCH(TRUE(),
[NETOF2]=0,"No Asbestos",
AND(risk_score>=1,risk_score<=8),"Low",
AND(risk_score>=9,risk_score<=15),"Medium",
AND(risk_score>=16,risk_score<=24),"High")
Hope it will be helpful.
Regards,
Marc
Thank you Mark this works perfectly. I accidently tried adding it a measure and not a measure columns 😀👍
Hi Marc thank you for your quick reponse. I am asuming that the variable is a measure. I dont have a measure for the Risk Score. This result is on the source table at a row by row level.
Also I am asuming the [NETOF2] should be risk_score
thanks
Richard
Hi Richard,
You are right at the second point, [NETOF2] should be risk_score, that was a mistake.
[Risk Score] is not a measure, it is calling the value of the column Risk Score, so it will work.
Regards,
Marc
Hi Richard,
You might try this:
Risk Category =
var risk_score=[Risk Score]
return
SWITCH(TRUE(),
[NETOF2]=0,"No Asbestos",
AND(risk_score>=1,risk_score<=8),"Low",
AND(risk_score>=9,risk_score<=15),"Medium",
AND(risk_score>=16,risk_score<=24),"High")
Hope it will be helpful.
Regards,
Marc
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |