Counting Text from One Table to Another

[rwoodward]

Hello - I need to create a DAX that allows me to count the number of times the names in [Table 2, column Name] appear in [Table 1, column Names]. The 3rd table is how Table 2 should appear when the DAX works correctly. Would love any help.

Table 1

Names

John Doe

John Doe, Jane Doe

Sam Garcia, Jane Doe, John Doe

Beth Baker

Table 2

Name

Beth Baker

Jane Doe

John Doe

Sam Garcia

Debra Smith

Table 2

Name	Count of Appearances in Table 1
Beth Baker	1
Jane Doe	2
John Doe	3
Sam Garcia	1
Debra Smith	0

[danextian]

Hi @rwoodward 

Please try this:

countrows = 
COUNTROWS (
    FILTER (
        VALUES ( T1[Names] ),
        CONTAINSSTRING ( T1[Names], EARLIER ( T2[Name] ) )
    )
)
+ 0

[Fowmy · Accepted solution]

@rwoodward 

Add the following calculated column to Table 2:

Count of Appearances= 
VAR NameToSearch = 'Table2'[Name]
RETURN
    COUNTROWS(
        FILTER('Table1', 
            SEARCH(NameToSearch, 'Table1'[Names], 1, 0) > 0
        )
    )

[rwoodward]

This worked perfectly! Thank you.