Forum Discussion

rwoodward's avatar
rwoodward
Frequent Visitor
1 year ago
Solved

Counting Text from One Table to Another

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

NameCount of Appearances in Table 1
Beth Baker1
Jane Doe2
John Doe3
Sam Garcia1
Debra Smith0
Need Help
  • Fowmy's avatar
    Fowmy
    1 year ago

    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
        )
    )

3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    1 year ago

    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
        )
    )
  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi rwoodward 

    Please try this:

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