Forum Discussion
Multiple conditions in SWITCH ()
Hi Team,
I have a Case Table and Priority Table, and have a column chart to show average of Time, and have another DAX code to have colour settings like if actual time is within 50% of target time, then show green, if within 80% then yellow, else red.
So it looks like:
Pirority = "A" && Actual time <= 2*1.5, "Green",
Pirority = "A" && Actual time <= 2*1.8, "Yellow",
Pirority = "A" && Actual time > 2*1.8, "Red",
Pirority = "B" && Actual time <= 4*1.5, "Green",
Pirority = "B" && Actual time <= 4*1.8, "Yellow",
Pirority = "B" && Actual time > 4*1.8, "Red",
Pirority = "C" && Actual time <= 6*1.5, "Green",
Pirority = "C" && Actual time <= 6*1.8, "Yellow",
Pirority = "C" && Actual time > 6*1.8, "Red",
Pirority = "D" && Actual time <= 8*1.5, "Green",
Pirority = "D" && Actual time <= 8*1.8, "Yellow",
Pirority = "D" && Actual time > 8*1.8, "Red",
I'm wondering if this is right logic and if there is any simple way to write this? Thanks!
Priority Table
| Priority | Target Time |
| A | 2 |
| B | 4 |
| C | 6 |
| D | 8 |
Case Table:
| Case ID | Priority | Actual Time |
| 001 | A | 1.5 |
| 002 | B | 4.8 |
| 003 | D | 22.2 |
| 004 | A | 0.7 |
| 005 | A | 5.5 |
| 006 | B | 3.3 |
| 007 | C | 6.1 |
| 008 | C | 2.4 |
| 009 | A | 2.2 |
| 010 | B | 9 |
2 Replies
Dooriya101 , Hope both tables are joined. You have to create a measure like, You can add additional conditions as per need
var _a = sum(Table2[Actual time])
var _p = Max(Table[Pirority])
return
Switch( True()
_= "A" && _a<= 2*1.5, "Green",
_= "A" && _a<= 2*1.8, "Yellow",
_= "A" && _a> 2*1.8, "Red"
// add other
)How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/1682539
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pane-66e0afcb15f3Hi Amitchandak, thenks for you reply and link provided. I've created DAX code as your advised, but when I try to apply in column chart, it doesn't allow me to choose it.
Below are my codes in product environment:
var _a = sum('SLA Closed'[SLA Response Duration (in Hours)])var _p = Max('Priority - SLA Response Target Hours'[Priority])returnSwitch(True(),_a= "Critical" && _a<= 2*1.05, "Green",_a= "Critical" && _a<= 2*1.08, "Yellow",_a= "Critical" && _a> 2*1.08, "Red",_a= "High" && _a<= 4*1.05, "Green",_a= "High" && _a<= 4*1.08, "Yellow",_a= "High" && _a> 4*1.08, "Red",_a= "Medium" && _a<= 8*1.05, "Green",_a= "Medium" && _a<= 8*1.08, "Yellow",_a= "Medium" && _a> 8*1.08, "Red",_a= "Low" && _a<= 16*1.05, "Green",_a= "Low" && _a<= 16*1.08, "Yellow",_a= "Low" && _a> 16*1.08, "Red")Thanks!
