Multiple conditions in SWITCH ()

[Dooriya101]

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

[amitchandak]

@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/...
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pan...

[Dooriya101]

Hi 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])

return

Switch(

    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!