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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cottrera
Post Prodigy
Post Prodigy

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 Prodigy
Post Prodigy

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

cottrera
Post Prodigy
Post Prodigy

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

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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