SWITCH FUNCTION WORKS FOR SOME CATEGORIES, BUT NOT ALL

[chudson002]

Good Evening Community,

Switch function does not work for all categories.  What am I missing? Should I use the Range column? Any assistance is greatly appreciated.  Please see the below:

KPI = SWITCH(

TRUE(),

'Count'[Threshold] = "ANY" && 'Count'[Average] > 60, "0",

'Count'[Threshold] = "<$50M" && 'Count'[Average] > 135, "0",

'Count'[Threshold] = ">$50M" && 'Count'[Average] > 240, "0",

'Count'[Threshold] = "<$250,000" && 'Count'[Average] > 30, "0",

'Count'[Threshold] = ">$250k but NTE $7M" && 'Count''[Average] > 45, "0",

'Count''[Threshold] = ">$7M" && 'Count'[Average] > 60, "0",

'Count'[Threshold] = "<$250k" && 'Count'[Average] > 35, "0",

'Count'[Threshold] = ">$250k but NTE $7M" && 'Count'[Average] > 60, "0",

'Count'[Threshold] = ">$7M" && 'Count'[Average] > 120, "0",

"1")

 Averages of (74, 20 & 31) KPI s/b "1"... 

    Category                     Range           Average       KPI

[Anonymous · Accepted solution]

Hi @chudson002 ,

Try using SUM('Count'[Average]) instead.
IF it's a measure, use SUMX('Count','Count'[Average]).

Also try IF() function.

KPI =
IF (
    ( 'Count'[Threshold] = "ANY"
        && 'Count'[Average] > 60 )
        || ( 'Count'[Threshold] = "<$50M"
        && 'Count'[Average] > 135 )
        || ( 'Count'[Threshold] = ">$50M"
        && 'Count'[Average] > 240 )
        || ( 'Count'[Threshold] = "<$250,000"
        && 'Count'[Average] > 30 )
        || ( 'Count'[Threshold] = ">$250k but NTE $7M"
        && 'Count'[Average] > 45 )
        || ( 'Count'[Threshold] = ">$7M"
        && 'Count'[Average] > 60 )
        || ( 'Count'[Threshold] = "<$250k"
        && 'Count'[Average] > 35 )
        || ( 'Count'[Threshold] = ">$250k but NTE $7M"
        && 'Count'[Average] > 60 )
        || ( 'Count'[Threshold] = ">$7M"
        && 'Count'[Average] > 120 ),
    "0",
    "1"
)

 If it still doesn't work, please share the pbix.

Best Regards,

Jay

[amitchandak]

@chudson002 , Make sure you handle overlapping ranges

, please check in bold

KPI = SWITCH(
TRUE(),
'Count'[Threshold] = "ANY" && 'Count'[Average] > 60, "0",
'Count'[Threshold] = "<$50M" && 'Count'[Average] > 135, "0",
'Count'[Threshold] = ">$50M" && 'Count'[Average] > 240, "0",
'Count'[Threshold] = "<$250,000" && 'Count'[Average] > 30 && 'Count'[Average] < 60, "0",
'Count'[Threshold] = ">$250k but NTE $7M" && 'Count'[Average] > 45, "0",
'Count'[Threshold] = ">$7M" && 'Count'[Average] > 60 && 'Count'[Average] < 120, "0",
'Count'[Threshold] = "<$250k" && 'Count'[Average] > 35, "0",
'Count'[Threshold] = ">$250k but NTE $7M" && 'Count'[Average] > 60, "0",
'Count'[Threshold] = ">$7M" && 'Count'[Average] > 120, "0",
"1")

[chudson002]

Good Day,

Revised per recommendation, indicators did not update.

Any suggestions?

V/R

chudson002