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.
I have two tables. Table 1 contains a list of keywords with the associated category. Table 2 contains a summary and description of work to be done. I want to calculate a column in Table 2 that will seach for values from the summary and description columns in Table 2 and then search the keywords column in Table 1 to see if any of the keywords are contained in the text. If found assign it the corresponding category for the keyword. If not, mark it Other.
Here is a simplified version of my data:
Table 1:
Keyword | Category |
WIRING | Network |
ROUTER | Network |
PC | Workstation |
VM | Server |
Table 2: I'd like the results to populate in the Category column
Summary | Description | Category |
NETWORK CABLING | THIS WORK IS TO REROUTE NETWORK WIRING | Network |
ROUTER CONFIG | THIS IS TO CONFIGURE ROUTER 2 | Network |
WORKSTATION REIMAGE | REIMAGE BOB'S PC | Workstation |
VM CONFIGURE | CONFIGURE VIRTUAL MACHINE | Server |
BATTERY REPLACEMENT | REPLACE THE BATTERIES IN BOB'S MOUSE | Other |
Solved! Go to Solution.
Hi @Anonymous
Try this for your calculated column:
Category = VAR _ResultCategory = CONCATENATEX ( VALUES ( Table1[Keyword] ); IF ( ( FIND ( Table1[Keyword]; Table2[Summary]; 1; 0 ) > 0 || FIND ( Table1[Keyword]; Table2[Description]; 1; 0 ) > 0 ); LOOKUPVALUE ( Table1[Category]; Table1[Keyword]; Table1[Keyword] ) ) ) RETURN IF(LEN(_ResultCategory)=0;"Other";_ResultCategory)
Hi @Anonymous
Try this for your calculated column:
Category = VAR _ResultCategory = CONCATENATEX ( VALUES ( Table1[Keyword] ); IF ( ( FIND ( Table1[Keyword]; Table2[Summary]; 1; 0 ) > 0 || FIND ( Table1[Keyword]; Table2[Description]; 1; 0 ) > 0 ); LOOKUPVALUE ( Table1[Category]; Table1[Keyword]; Table1[Keyword] ) ) ) RETURN IF(LEN(_ResultCategory)=0;"Other";_ResultCategory)
Works perfectly. Thanks!
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 |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |