Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Outliers in DAX

Hi, I'm working on a dataset that has 2 (or more) months worth of data. We mark the outliers manually in Excel by sorting the resolution hrs and then getting 5% of the total rows per month and then m...
  • V-lianl-msft's avatar
    V-lianl-msft
    4 years ago

    Hi Anonymous ,

     

    Try to create a new column like below:

    outlier_test = 
var rank_asc = RANKX(FILTER('Table',EARLIER('Table'[Month Restored])='Table'[Month Restored]),'Table'[Restored Hours],,ASC,Dense)
var rank_desc = RANKX(FILTER('Table',EARLIER('Table'[Month Restored])='Table'[Month Restored]),'Table'[Restored Hours],,DESC,Dense)
var count_row = COUNTROWS(FILTER('Table',EARLIER('Table'[Month Restored])='Table'[Month Restored]))*0.05
return IF(rank_asc<=count_row||rank_desc<=count_row,"Outlier","Normal")

     


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

V-lianl-msft's avatar
V-lianl-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi Anonymous ,

 

Try to create a new column like below:

outlier_test = 
var rank_asc = RANKX(FILTER('Table',EARLIER('Table'[Month Restored])='Table'[Month Restored]),'Table'[Restored Hours],,ASC,Dense)
var rank_desc = RANKX(FILTER('Table',EARLIER('Table'[Month Restored])='Table'[Month Restored]),'Table'[Restored Hours],,DESC,Dense)
var count_row = COUNTROWS(FILTER('Table',EARLIER('Table'[Month Restored])='Table'[Month Restored]))*0.05
return IF(rank_asc<=count_row||rank_desc<=count_row,"Outlier","Normal")

 


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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you for this! Worked like a charm. 🙂