Forum Discussion
Calculate Outliers Within Data Model (IQR method)
I have manually created measures to determine the upper and lower bounds ([Q1-1.5*IQR, Q3+1.5*IQR]) of my dataset (2551 records). but have no means of looping back the values as a calculated column to determine if the adjacent value is outside the range to flag as an outlier for later filtering. Is there a DAX method that allows me to calculate the IQR in context of the data model?
5 Replies
What is "the adjacent value" ? Do you have an index column or some other deterministic way to sort your data?
The dataset was imported via data query from a SQL database, so I have a primary key I can use to uniquely identify individual records.
That would be good - you can then use the "previous row" pattern to run your outlier detection.
Have you considered using the built-in anomaly detection feature that was recently introduced?
I am not familiar with that feature, could you refer me to its documentation? I don't use much of Power BI to be honest, seems like it could save me some time.
Figure I'll add to stay in line with the OP, by "adjacent value" I meant the data value of each record in the dataset. I want a comparator column to flag if the individual value adjacent to it is outside that range (an outlier) so I can filter from my pivots as it's skewing my metrics. Seems the issue of trying to compute the flag using measures is that Power Pivot doesn't seem to be able to compute the indvidual measures and calculated column in parallel, and will only work if I have no outside references (no calculated measures). But I was able to solve it with a verbose, compound IF(OR... statement.
