Forum Discussion

pbiforum123's avatar
pbiforum123
Post 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 Conv...
Need Help
  • pbiforum123's avatar
    pbiforum123
    Post 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
      Post 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