Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
@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
User | Count |
---|---|
107 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |