DAX scoring if column values between set criteria

[cottrera]

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

[cottrera]

Thank you Mark this works perfectly. I accidently tried adding it a measure and not a measure columns 😀👍

[cottrera]

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

[Anonymous]

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

[mflo · Accepted 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