Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!See when key Fabric features will launch and what’s already live, all in one place and always up to date. Explore the new Fabric roadmap
05-16-2023 09:25 AM - last edited 05-16-2023 09:36 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