skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
    • Dynamics 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Overview
    • Find consulting services
    • Partner showcase
    • Find a partner
    • Become a partner
    • Instructor-led training
    • Getting started
      • Overview
      • Online workshops
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Microsoft Power BI Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power BI
    • Desktop
    • Service
    • Report Server
    • Power Query
    • Mobile Apps
    • Developer
    • DAX Commands and Tips
    • Custom Visuals Development Discussion
    • Health and Life Sciences
    • Power BI Spanish Community
    • Translated Spanish Desktop
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Training and Consulting
    • Instructor Led Training
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    • Ideas
    • Custom Visuals Ideas
    • Issues
    • Issues
    • Events
    • Upcoming Events
    • Community Engagement
    • T-Shirt Design Challenge 2023
    • Community Blog
    • Power BI Community Blog
    • Custom Visuals Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • Quick Measures Gallery
    • Anomaly Detection - Local Outlier Factor

    Anomaly Detection - Local Outlier Factor

    05-16-2023 09:25 AM - last edited 05-16-2023 09:36 AM

    Super User Greg_Deckler
    Super User
    247 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    Anomaly Detection - Local Outlier Factor

    ‎05-16-2023 09:25 AM

    Demonstrates how to implement the Local Outlier Factor (LOF) anomaly detection algorithm in DAX.

     

    LOF Measure = 
        VAR __ID = MAX('Table'[ID])
        VAR __Table0 = ALL('Table')
        VAR __Table = 
            FILTER( 
                GENERATE( 
                    SELECTCOLUMNS( __Table0,"ID1",[ID] ) , 
                    SELECTCOLUMNS( __Table0,"ID2",[ID] ) 
                ), 
                [ID1] <> [ID2]
            )
        VAR __Table1 = 
            ADDCOLUMNS(
                __Table,
                "ID3", IF([ID1] < [ID2], [ID1], [ID2]),
                "ID4", IF([ID1] > [ID2], [ID1], [ID2])
            )
        VAR __Table3 = 
            ADDCOLUMNS(
                DISTINCT( SELECTCOLUMNS( __Table1, "ID1", [ID3], "ID2", [ID4] ) ),
                "MD",
                    VAR __ID1 = [ID1]
                    VAR __ID2 = [ID2]
                    VAR __x1 = MAXX(FILTER(__Table0, [ID] = __ID1), [X])
                    VAR __y1 = MAXX(FILTER(__Table0, [ID] = __ID1), [Y])
                    VAR __x2 = MAXX(FILTER(__Table0, [ID] = __ID2), [X])
                    VAR __y2 = MAXX(FILTER(__Table0, [ID] = __ID2), [Y])
                    VAR __Result = ABS(__x1 - __x2) + ABS(__y1 - __y2)
                RETURN
                    __Result
            )
        VAR __Table4 = 
            ADDCOLUMNS(
                ADDCOLUMNS(
                    SELECTCOLUMNS(
                        __Table0,
                        "ID",[ID],"X",[X],"Y",[Y]
                    ),
                    "k-MD",
                        VAR __ID = [ID]
                        VAR __Table = FILTER(__Table3, [ID1] = __ID || [ID2] = __ID)
                        VAR __kMD = MAXX(TOPN([k], __Table, [MD],ASC),[MD])
                    RETURN
                        __kMD         
                ),
                "K-neighborhood",
                    VAR __kMD = [k-MD]
                    VAR __ID = [ID]
                    VAR __Table = FILTER('Table 2', [ID1] = __ID || [ID2] = __ID)
                    VAR __Result = COUNTROWS( FILTER( __Table, [MD] <= __kMD ) )
                RETURN
                    __Result
            )
        VAR __Table5 = 
            ADDCOLUMNS(
                __Table4,
                "LRD",
                    VAR __kMD = [k-MD]
                    VAR __Kneighborhood = [K-neighborhood]
                    VAR __ID = [ID]
                    VAR __Table = FILTER('Table 2', ( [ID1] = __ID || [ID2] = __ID ) && [MD] <= __kMD )
                    VAR __Table1 = 
                        ADDCOLUMNS( 
                            __Table, 
                            "MaxMD", 
                                    VAR __LookupValue = IF([ID1] = __ID, [ID2], [ID1])
                                    VAR __LookupKMD = MAXX(FILTER(__Table4, [ID] = __LookupValue), [k-MD])
                                RETURN
                                MAX( [MD], __LookupKMD) )
                    VAR __Result = DIVIDE( 1, DIVIDE( SUMX( __Table1, [MaxMD] ), __Kneighborhood ))
                RETURN
                    __Result
            )
        VAR __Table6 = 
            ADDCOLUMNS(
                 __Table5,
                 "LOF",
                    VAR __kMD = [k-MD]
                    VAR __Kneighborhood = [K-neighborhood]
                    VAR __LRD = [LRD]
                    VAR __ID = [ID]
                    VAR __Table = FILTER(__Table3, ( [ID1] = __ID || [ID2] = __ID ) && [MD] <= __kMD )
                    VAR __IDs = DISTINCT( FILTER( UNION( SELECTCOLUMNS( __Table, "ID", [ID1] ), SELECTCOLUMNS( __Table, "ID", [ID2] ) ), [ID] <> __ID ) )
                    VAR __Table1 = FILTER( __Table5, [ID] IN __IDs )
                    VAR __Result = DIVIDE( SUMX( __Table1, [LRD] ), __Kneighborhood ) * DIVIDE(1, __LRD)
                RETURN
                    __Result
            )
        VAR __Result = MAXX(FILTER(__Table6, [ID] = __ID), [LOF])
    RETURN
        __Result

     

    eyJrIjoiYjRiMjUyODMtZmFjNy00MjVmLTk5OGEtOWNiMDc0NGNmMGQ0IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Local Outlier Factor.pbix
    Labels:
    • Labels:
    • Mathematical
    • Other
    Message 1 of 1
    247 Views
    0
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices