Forum Discussion

Redacted_VAR's avatar
Redacted_VAR
Icon for Helper I rankHelper I
2 years ago
Solved

Non Exact Lookup Column Across Two Tables

Hi There,

 

i have two tables, one is attendees to an event, and the other is a master customer list:

 

EventTable:

Customer NameFeedback Score
Bobs Burgers8
Fine Foods 7
Smart Shopping Inc9

 

MasterCustomer:

Customer NameIndustry
Bobs Burgers PLCIndustry 1
Fine Foods LimitedIndustry 2
Smart Shopping IncIndustry 3

 

I want to converge these tables, by creating a relationship between the two customer name columns, however they're not an exact match as the event table misses the formal company name. currently only the last customer is an exact match so a lookup column returns a blank for the first two - how do i create a lookup that looks for the presence of the text in event customer column inside the master customer column?

i want the following table as an output:

Customer NameFeedback ScoreIndustry
Bobs Burgers PLC8Industry 1
Fine Foods Limited7Industry 2
Smart Shopping Inc9Industry 3

 

thanks in advance!

 

Need Help
  • DataInsights's avatar
    DataInsights
    2 years ago

    Redacted_VAR,

     

    Try this DAX calculated column in EventTable:

     

    Industry = 
VAR vCustomerName = EventTable[Customer Name]
VAR vTargetRow =
    FILTER (
        MasterCustomer,
        CONTAINSSTRING ( MasterCustomer[Customer Name], vCustomerName )
    )
VAR vResult =
    MAXX ( vTargetRow, MasterCustomer[Industry] )
RETURN
    vResult

     

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    2 years ago

    Redacted_VAR,

     

    Try this DAX calculated column in EventTable:

     

    Industry = 
VAR vCustomerName = EventTable[Customer Name]
VAR vTargetRow =
    FILTER (
        MasterCustomer,
        CONTAINSSTRING ( MasterCustomer[Customer Name], vCustomerName )
    )
VAR vResult =
    MAXX ( vTargetRow, MasterCustomer[Industry] )
RETURN
    vResult