Searching Wild card String between two non related tables

[bukhari1979]

Hi, I 've got two tables (not related to each other). Table1 has survey comments in text format and table2 has hot-words to categorize a survey into three main categories namely POSITIVE, NEGATIVE & GENERAL. I have written the code below but unfortunately the output is pretty weird as the output insert multiple results against every comment..probably it keeps looking into the comments and whenever it finds a match it insert the result in short ..it does the match but returns multiple values against a single record. Any help & correction will be highly appreciated Here is the code

CommentGrade = 
VAR Matches = 
CALCULATETABLE(
GENERATE(
Sheet1,FILTER( 
CSATAllData,
SEARCH(
[Perspective Name],[Comments],
1,
0
)>0)

)
)
RETURN 
CONCATENATEX(
Matches,
[PerspectiveGrade],",")

[v-shex-msft]

HI @bukhari1979,

Please provide some sample data for test, I think it will be help for coding formula.

Regards,

Xiaoxin Sheng

[bukhari1979]

Current Situation

Hi @v-shex-msft hyg dear

[v-shex-msft · Accepted solution]

Hi @bukhari1979,

You can try to use below measures to get match key from other table.

Sample:

Matched = 
VAR _text =
    SELECTEDVALUE ( Table2[Text] )
RETURN
    CONCATENATEX (
        FILTER ( ALL ( KeyTable ), SEARCH ( [Key], _text, 1, -1 ) > 0 ),
        [Key],
        ","
    )


Matched Category = 
VAR _text =
    SELECTEDVALUE ( Table2[Text] )
RETURN
    CONCATENATEX (
        CALCULATETABLE (
            VALUES ( KeyTable[Category] ),
            FILTER ( ALL ( KeyTable ), SEARCH ( [Key], _text, 1, -1 ) > 0 )
        ),
        [Category],
        ","
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi, I am relatively new to Power BI and this is my first time posting.

I tried to adapt the above solution to simply produce a table showing the number of times a specific term from my mapping table appeared in my base data table. Unfortunately it didn't perform a wildcard match, just an exact match. How do I get it to do a wildcard match? 

Here's the code that I used (not sure if I'm posting correctly here): 

test1 =

VAR Matches =

CALCULATETABLE(generate('Job Function Map', Filter('Base Data',SEARCH('Job Function Map'[Job Function Codes], 'Base Data'[Job Function],1,0)>0)))

RETURN COUNTROWS('Base Data')

Here's a sample of the data for reference: 

Map
Code	Job Function 2019
RESPNBLT1	Corporate / General Management
RESPNBLT2	Technical / Engineering
RESPNBLT3	Administration
RESPNBLT4	Business Development / Project Management
RESPNBLT5	Consulting
RESPNBLT7	Other (please specify)
etc.

Data
Row	Job Function Codes
1	RESPNBLT4, RESPNBLT8, RESPNBLT19, RESPNBLT16, RESPNBLT32
2	RESPNBLT14
3	RESPNBLT17
4	RESPNBLT30, RESPNBLT14, RESPNBLT12, RESPNBLT28

Any tips or suggestions would be greatly appreciated.

Thanks!