Filter a Measure Value

[harshagraj]

Hello All,

1. I have created a table for Measure Names to use a Filter,(CycleTime1,CycleTime2)

2.Created Two Columns  CycleTime1 = Datediff(Time2,Time1,Sec), CycleTime2 = Datediff(Time3,Time2,Sec)

3.Created a Measure Cycletime 

CycleTime = IF(

HASONEVALUE('MeasureTable'[Measures]),

SWITCH(

VALUES('MeasureTable'[Measures]),

"CycleTime1 ,AVERAGE(CTM_Timestamps[CycleTime1]),

"CycleTime2 ,AVERAGE(CTM_Timestamps[CycleTime2]),

AVERAGE(CTM_Timestamps[CycleTime1])

))

4. To Achieve Control Chart I have calculated Average,STD Dev,UCL,LCL

Average = CALCULATE(AVERAGEX(CTM_Timestamps,[CycleTime]),ALLSELECTED(CTM_Timestamps))

STDEV = CALCULATE(STDEVX.P(CTM_Timestamps,[CycleTime]),ALLSELECTED(CTM_Timestamps))

UCL = [Average]+[STDEV]*3

LCL = [Average]-[STDEV]*3

5.I have plotted line chart

6. I need a filter to Select UCL and LCL and I tried the below one but i am getting circular error.

UCL Filter = IF(VALUE([CycleTime])>[UCL],"Crossed UCL","Not Crossed")

Please help.

[amitchandak]

@harshagraj , change these measures like and check

Average = CALCULATE(AVERAGEX(CTM_Timestamps, IF(
HASONEVALUE('MeasureTable'[Measures]),
SWITCH(
VALUES('MeasureTable'[Measures]),
"CycleTime1 ,AVERAGE(CTM_Timestamps[CycleTime1]),
"CycleTime2 ,AVERAGE(CTM_Timestamps[CycleTime2]),
AVERAGE(CTM_Timestamps[CycleTime1])
))),ALLSELECTED(CTM_Timestamps))

STDEV = CALCULATE(STDEVX.P(CTM_Timestamps, IF(
HASONEVALUE('MeasureTable'[Measures]),
SWITCH(
VALUES('MeasureTable'[Measures]),
"CycleTime1 ,AVERAGE(CTM_Timestamps[CycleTime1]),
"CycleTime2 ,AVERAGE(CTM_Timestamps[CycleTime2]),
AVERAGE(CTM_Timestamps[CycleTime1])
))),ALLSELECTED(CTM_Timestamps))

[harshagraj]

Hello Amit unfortunately I am getting NAN errors if i do that 😞

[amitchandak]

@harshagraj , Try like

UCL Filter = IF(([CycleTime])>[UCL],"Crossed UCL","Not Crossed")

or in Average , STDEV copy the calculation CycleTime , instead of using it

[harshagraj]

Hi thanks for the reply. I tried the UCL Filter but getting same error. I didnt understand the 2nd Point.
User Just need a UCL filter to see which are the spiked ones.