Determine Overall Risk by Date

[MarcoUrbanic]

Not sure the best way to do this in Power BI, and if I should do it using DAX or M.  I have a table with a Date Column, items, and risk for each item.  What I want to do is set an overall risk for the date using the following rules in order:

If any Item is RED, the Overall Risk = RED

If any Item is YELLOW (and there are no RED items), the Overall Risk = YELLOW

If all Items are GREEN, the Overall Risk = GREEN

Date	Item ID	Risk	Overall Risk
7/20/2019	123	Green	Red
7/20/2019	124	Red
7/20/2019	125	Yellow
7/21/2019	126	Green	Yellow
7/21/2019	127	Green
7/21/2019	128	Yellow
7/22/2019	129	Green	Green
7/22/2019	130	Green
7/22/2019	131	Green

Thanks in advance for the suggestions!

[v-xuding-msft · Accepted solution]

Hi @MarcoUrbanic ,

I created a measure you can have a try.

Measure = 
var a = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Risk] = "Red"))
var b = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Risk] = "Yellow"))
return
IF(NOT(ISBLANK(a)),"Red",IF(NOT(ISBLANK(b)),"Yellow","Green"))

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.