Forum Discussion

sh_Himanshu's avatar
sh_Himanshu
Regular Visitor
1 year ago

Nelson Rule Implementation in Power BI

I have to create a control chart which includes Mean , Std dev & control limits as well along with that we need to implement Nelson Rules in it which shows different violated points in diff colors. ...
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi sh_Himanshu ,

Based on the description, try to add a secondary ranking based on Batch ID to make sure unique ranking. Use the following DAX formula.

Nelson_Rule2 = 
VAR CurrentIndex = RANKX(ALL('Table'), 'Table'[Mfg_Date] & 'Table'[Batch_ID], , ASC, DENSE)
VAR PreviousValues = CALCULATETABLE(
    TOPN(8, FILTER(ALL('Table'), RANKX(ALL('Table'), 'Table'[Mfg_Date] & 'Table'[Batch_ID], , ASC, DENSE) < CurrentIndex), 'Table'[Mfg_Date], ASC)
)
VAR SameSideCount = COUNTROWS(
    FILTER(PreviousValues, SIGN('Table'[Numer_Values] - [Mean_Numer_Values]) = SIGN(EARLIER('Table'[Numer_Values] - [Mean_Numer_Values])))
)
RETURN IF(SameSideCount >= 8, 1, 0)

 

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.