cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## 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(

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

1 ACCEPTED SOLUTION
Community Support

Hi @chudson002 ,

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
3 REPLIES 3
Community Support

Hi @chudson002 ,

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Super User

@chudson002 , Make sure you handle overlapping ranges

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")

Frequent Visitor

Good Day,

Revised per recommendation, indicators did not update.

Any suggestions?

V/R

chudson002

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors