Forum Discussion

sv11's avatar
sv11
Helper I
7 years ago
Solved

Tables Join

Hi,   I have 2 different tables pulled from different system:         1. CRM - Contact         2. Phone Call data   There is no direct link between these 2 systems except "Phone Number".   Is...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi sv11 ,

     

    Based on your data sample, you could do some change for your CRM data model in Query Editor.

     

    You could unpivot the columns for Phone1,Phone2,Phone3 and then rename the value to PhoneNo.

     

    Then you could create the relationship between the tables with PhoneNo.

     

     

    More details, you could refer to my attachment.

     

    Best Regards,

    Cherry

sv11's avatar
sv11
Helper I
7 years ago

Hi v-piga-msft ,

 

Please see the 2 pics below:

 

1. Call Data - is the data from our phone systrem which tracks all calls made by sales team

 

2. CRM Data - is the contact information of our clients, source for sales team

 

Thanks

Aron_Moore's avatar
Aron_Moore
Solution Specialist
7 years ago

If the number of phone # columns is fixed you could try a calculated or custom column.


In Query editor it would something like PhoneKey = If Phone1 <> "" then Phone1 else if Phone2 <> ""....etc

In DAX you could use SWITCH()

 

Then join or lookup using the PhoneKey column.