Forum Discussion

pbiforum123's avatar
pbiforum123
Icon for Post Patron rankPost Patron
3 years ago

help needed in fuzzy mapping

 

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...

 

 

Need Help

3 Replies

    • pbiforum123's avatar
      pbiforum123
      Icon for Post Patron rankPost Patron
      3 years ago

      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's avatar
        pbiforum123
        Icon for Post Patron rankPost Patron
        3 years ago

        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