Forum Discussion

chudson002's avatar
chudson002
Frequent Visitor
4 years ago
Solved

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 = SWIT...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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's avatar
chudson002
Frequent Visitor
4 years ago

Good Day,

Revised per recommendation, indicators did not update.

Any suggestions?

 

V/R

chudson002