cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cottrera
Post Patron
Post Patron

DAX scoring if column values between set criteria

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 RefLocationCurrent StatusRisk ScoreRisk Category (Expected results with Dax)
26171Room 1No Asbestos Detected0No Asbestos
26171Room 1No Asbestos Detected22High
26171Room 3No Asbestos Detected0No Asbestos
26171HallwayAsbestos Confirmed13Medium
26171KitchenNo Asbestos Detected0No Asbestos
26171Bathroom 1Asbestos Confirmed12Medium
26171Bathroom 2Asbestos Confirmed12Medium
26171LoftNo Asbestos Detected0No Asbestos
26171CellarAsbestos Confirmed14Medium
26171RoofNo Asbestos Detected0No Asbestos
26171Walls 1No Asbestos Detected1Low
26171Walls2No Asbestos Detected2Low

 

This is the Criteria I have 

OrderMinMaxRisk Category
100No Asbestos
218Low
3915Medium
41624High

 

thank you

Richard

1 ACCEPTED SOLUTION
mflo
New Member

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

View solution in original post

4 REPLIES 4
cottrera
Post Patron
Post Patron

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

cottrera
Post Patron
Post Patron

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.

 

cottrera_0-1631704703883.png

Also I am asuming the [NETOF2] should be risk_score

 

thanks

 

Richard

m_flo
Regular Visitor

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
New Member

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

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!