Searching for substring in another table (the two are related)

[Anonymous]

Hi ! My first time here , so please be patient (:

I have three tables: A,B,C
tables B and C are related to A (many:many)
after adding the three to the model , the data looks like :

             A                                        B                                  C
 customer_id(int), ip (str)       ip(str),isp(str)       customer_id(int), valid_domain(str) 

(A and B are joiuned on ip, A and C are joined on customer_id)

now, after setting the relashionship I want to create  a caculated column with "1" values when 'isp' is a substring of 'valid_domain' and "0" if not. 
I'm familiar with the 'search' function , but not sure how to apply it across tables.
Any asistance will be appreciated ! 

  

       

[v-lili6-msft · Accepted solution]

hi @Anonymous 

First, you could use RELATEDTABLE and CONCATENATEX Function to get the related value (valid_domain(str) )  in one cell for tableB like below:

Related values = CONCATENATEX(RELATEDTABLE(C),C[valid_domain],",")

Then use 'search' function to create  a caculated column with "1" values when 'isp' is a substring of 'valid_domain' and "0" if not in table B.

You can nest [related values] into below formula:

Result = SEARCH(B[isp],CONCATENATEX(RELATEDTABLE(C),C[valid_domain],","),1,0)

and here is sample pbxi file, please try it.

Regards,

Lin

[Greg_Deckler]

You could create a temporary table (VAR) that contains the records from the other table, RELATEDTABLE or RELATED. Then you could use ADDCOLUMNS to add a column to this table that uses SEARCH. The rest should be pretty straight-forward once you have that.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490