Forum Discussion
Determine Overall Risk by Date
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!
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.
1 Reply
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.