cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JorgeLuizMS
New Member

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 II
Super User II

=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 II
Super User II

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

View solution in original post

Thank you so much!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.