cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JorgeLuizMS
Regular Visitor

Create a Calculated column with a partial match.

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:

 

F_CALLS.pngD_CLIENTS.png 

 

 

Someone has an idea how make this?

 

thank you in advance

 

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

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

View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
Super User

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

Thank you so much!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors