Create a Calculated column with a partial match.

[JorgeLuizMS]

Hi people!

I have the following situation, would anyone be able to give me some help.

I have two tables:

D_CLIENTS = table with the registration of customers.

F_CALLS = table that records the phone calls made.

In the D_CLIENTS table, there are customers who have a registered phone, two others, up to three phones, all within the same column and the numbers are concatenated. There are also customers who do not have a registered phone, which is not a problem.

I need to make a statistic of customers who receive calls and I need to assemble graphs of call amounts per client. For this I thought of creating a calculated column that informs the client of that call.

I created a function in DAX that removes all punctuation, symbols and spaces that have both the customer number and the dialed number.

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D_CLIENTES[TELEFONE],"-",""),"/","")," ",""))

Now I would need to build a DAX formula that would find the dialed number within the string that is the customer's phone number field and return me the customer's name to populate the table.

For example:

Dialed number: 333

Customer Telephone Number: 123/ 234 /333

As 333 is inside 123/ 234 /333 then it will return the name of the customer to populate the calculated column.

An example of the data is this:

 

Someone has an idea how make this?

thank you in advance

[wdx223_Daniel · Accepted solution]

=MAXX(FILTER(D_CLIENTS,FIND(F_CALLS[CALL NUMBER],D_CLIENTS[TELEFONE],,0)),D_CLIENTS[NOME])

[JorgeLuizMS]

Thank you so much!