Forum Discussion
SWITCH FUNCTION WORKS FOR SOME CATEGORIES, BUT NOT ALL
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(
Averages of (74, 20 & 31) KPI s/b "1"...
Category Range Average KPI
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
3 Replies
- amitchandak
Super User
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")Good Day,
Revised per recommendation, indicators did not update.
Any suggestions?
V/R
chudson002
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
