Forum Discussion
Cross reference another table to retrieve text value
Hello!
I am trying to reference a table that has a list of activities to a table that has a list of purchases that "should" have the activities as the first characters of the description. E.g.
Table of Activities
| Activities | WBS |
| ABC.1.1 | ABC |
| ABC.2.1 | ABC |
| DEF.1.1 | DEF |
Table of purchases
| WBS | Description | Amt |
| ABC | ABC.1.1 Stuff | $20 |
| DEF | DEF.1.1. things | $33 |
| ABC | ABC.2.1 More stuff | $44 |
| ABC | Freight | $10 |
Table to look like
| WBS | Description | Amt | Activity |
| ABC | ABC.1.1 Stuff | $20 | ABC.1.1 |
| DEF | DEF.1.1. things | $33 | DEF.1.1 |
| ABC | ABC.2.1 More stuff | $44 | ABC.2.1 |
| ABC | Freight | $10 | Unknown1 |
Is there a way to do this in Power BI Desktop?
Thanks!
7 Replies
Try a new column like
New column = Maxx(filter(Table1,Table1[WBS]= Table2[WBS] && search(Table2[Activities],table1[Description],,0)>0),Table1[Activities])
Thanks for your reply !
I'm getting an error which is higlighting "search". It says "Token Literal Expected"
Hi PowerBIGpig ,
We can use the following measure to meet your requirement:
Activity = COALESCE ( CALCULATE ( MAX ( 'Activities'[Activities] ), FILTER ( 'Activities', 'Activities'[WBS] = 'purchases'[WBS] && CONTAINSSTRING ( 'purchases'[Description], 'Activities'[Activities] ) ) ), "Unknown1" )
By the way, PBIX file as attached.
Best regards,
Hi PowerBIGpig ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,
