topic Outlier Detection and Removal in DAX Commands and Tips

Outlier Detection and Removal

Anonymous — Thu, 04 Apr 2024 11:24:19 GMT

Hi,
I have a column called FA in my table. I have created multiple DAX to write Quartile 1, Quartile 3, IQR, Lower Limit and Upper Limit using mathematical calculations. I have detected Outliers as well.
I want to remove those outliers and calculate 5th, 10th, 25th, 50th percentile of the values that are not outliers. Can anyone help me removing outliers and calculate all the percentiles.
DAX for Outlier Treatment:

Outlier Formula =

Var Q3 = PERCENTILE.INC('Goal Setting Consolidated Data'[FSA],0.75)

Var Q1 = PERCENTILE.INC('Goal Setting Consolidated Data'[FSA],0.25)

var IQR = Q3-Q1

var upper = Q3+(IQR*1.5)

var lower = Q1-(IQR*1.5)

var LL = CALCULATE(lower,ALL('Goal Setting Consolidated Data'[employee_id],'Goal Setting Consolidated Data'[FSA]))

Var UL = CALCULATE(upper,ALL('Goal Setting Consolidated Data'[employee_id],'Goal Setting Consolidated Data'[FSA]))

return

SWITCH(TRUE(),SELECTEDVALUE('Goal Setting Consolidated Data'[FSA])> UL , "Outlier",

SELECTEDVALUE('Goal Setting Consolidated Data'[FSA])<LL , "Outlier",

BLANK())

Re: Outlier Detection and Removal

Anonymous — Fri, 05 Apr 2024 01:44:48 GMT

Hi Anonymous

You can try to add a new column to use if() function to mark the Outlier, so that you can use the filter() function to calculate the percentile which you want.

Similarly that you can use the filter() function and calculatetable() function to create a new table without these values that you don't want.

Best Regards

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

Re: Outlier Detection and Removal

Anonymous — Fri, 05 Apr 2024 06:24:42 GMT

Hi Anonymous
Thank you so much for the suggestions!!. I have implemented this by creating columns for Q1, Q2, IQR, LL,UL and the outlier detection using if statement. However. In order to create the Q1 and Q3, I have to make sure it won't change row level. I need the same Q1 and Q2 (aggregated) for each Skill and Month.
Calculation for Q1:

Q1 Calc = CALCULATE(PERCENTILE.INC('Goal Setting Consolidated Data'[FSA], 0.25), ALLEXCEPT('Goal Setting Consolidated Data','Goal Setting Consolidated Data'[yrmonth],'Goal Setting Consolidated Data'[skill]))
But I would like to know how will I create measures using CALCULATETABLE and FILTER. If possible, could you please help me with writing those measures.
Response would be highly appreciated!!
Thanks