help needed in fuzzy mapping

[pbiforum123]

I have mapping table as below

Campaign Objective Campaign Code
App Installs APPIN
App re-engagements APPRE
Awareness AWA
Calibration CALIB
Catalogue Sales CATSA
Consideration CONSI
Conversion CON
Engagement ENG
Event Responses EVENT
Followers FOLLW
Followers FOLLOW

I have campaignObjective column as below.

CONSI - O-2KJGT
ENG - Copy
AWA EUR
LEADGEN
CONSID
AWA-TRAFFIC O-2YYQ9
AWA august
CONSI - O-2WMGP
CONS
Trial
CONSI - O-2WMBY
AWA - O-2VMNY
AWAO-2WTPW

I need a calculated code which does fuzzy search as below..

input: 1
AWA-TRAFFIC O-2YYQ9

output: 1
Awareness

input: 2
AWA EUR

output: 2
Awareness

input: 3
CONSI - O-2WMGP

output: 3
Consideration

Please help me on this...

[Greg_Deckler]

@pbiforum123 I wrote a fuzzy matching algorithm in DAX once: Fuzzy - Microsoft Power BI Community

[pbiforum123]

@Greg_Deckler  I saw the code but I am not able to understand how it works. If you can let me know which column i need to replace with the column that I have, I think that will be helpful. So that I can update your column with the columns that i have and check the result.

Please help

[pbiforum123]

@Greg_Deckler I have updated the code as below but i am not getting expected output so looking into it.

Also I feel that we need to create the calculated column instead of measure since I need matched "Campaign Objective" value based for all the rows. 

Fuzzy =

VAR __MatchWord = MAX(dim_campaign_pca[campainObjective])

VAR __CleanMatchThreshold = 4

VAR __KillThreshold = 3

VAR __FuzzyThreshold1 = .4

VAR __FuzzyThreshold2 = .8

VAR __WordSearchTable =

GENERATE(

'Fuzzy Mapping',

VAR __Word = MAXX(FILTER('Fuzzy Mapping',[Campaign Code]=EARLIER('Fuzzy Mapping'[Campaign Code])),[Campaign Objective])

RETURN ADDCOLUMNS(GENERATESERIES(3,LEN(__Word),1),"Search",LEFT(__Word,[Value]),"Original",__Word)

)

VAR __Table =

FILTER(

ADDCOLUMNS(

__WordSearchTable,

"Match",SEARCH([Search],__MatchWord,,BLANK())

),

NOT(ISBLANK([Match]))

)

VAR __Max = MAXX(__Table,[Value])

VAR __Match = MAXX(FILTER(__Table,[Value]=__Max),[Search])

VAR __Proposed =

IF(

LEN(__Match)<=__CleanMatchThreshold,

SWITCH(TRUE(),

//__Clean2 = "ABB",__Clean2,

COUNTROWS(FILTER(__Table,[Value]=__Max))>1,BLANK(),

LEN(__Match) <= __KillThreshold,BLANK(),

LEN(__Match) = LEN(__MatchWord),__Match,

LEN(__Match)/LEN(__MatchWord)>__FuzzyThreshold1 && SEARCH(__Match,__MatchWord,,0)=1,__Match,

LEN(__Match)/LEN(__MatchWord)>__FuzzyThreshold2,__Match,

BLANK()

),

SWITCH(TRUE(),

__Match = "Blue Cross" || __Match = "Blue Cross ",__Match,

LEN(__Match)/LEN(__MatchWord)<__FuzzyThreshold2 && SEARCH(__Match,__MatchWord,,0)<>1,BLANK(),

__Match

)

)

VAR __Clean1 = IF(RIGHT(__Proposed,1)="(",LEFT(__Proposed,LEN(__Proposed)-1),__Proposed)

VAR __Clean2 = IF(RIGHT(__Clean1,1)=" ",LEFT(__Clean1,LEN(__Clean1)-1),__Clean1)

RETURN

__Clean2