Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 !
Solved! Go to 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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |