Finding the amount of values in table 1 that are matching values from table 2

[esiniapkina]

Hi 

I am trying to find equivalent of Excel formula in DAX:

Count how many specific words (from keywords table) is in mentioned in a cell from the range from different table 

=SUMPRODUCT((LEN(txt)-LEN(SUBSTITUTE(UPPER(txt),UPPER(range),""))) / LEN(UPPER(range)))

So i am kinds looking for solving similar task with matching words from different table:

 I need similar formula but it should show me the number of words, not just TRUE or FALSE

 [Is this company a metals company] =

  =IF(
      SUMX(MatchList,
           FIND(
                UPPER(MatchList[Keyword]),
                UPPER(Companies[Company])
                ,,0
               )
          ) > 0,
      “YES!”,
      “Probably Not”
     )

 Thank you

[v-lili6-msft · Accepted solution]

HI,@esiniapkina

     After my research, you can do these follow my steps as below:

Step 1:

add index column in Edit Queries and then add a calculate column:

Hasvalue = SUMX(MatchList,
           FIND(
                UPPER(MatchList[Keyword]),
                UPPER(Companies[Company])
                ,,0
               )
          )

Step2:

Cross join the two table

Table = CROSSJOIN(Companies,MatchList)

Step3;

add these column

Sub = SUBSTITUTE('Table'[Company],'Table'[Keyword],"")

Count = (LEN('Table'[Company])-LEN('Table'[Sub]))/LEN('Table'[Keyword])

C1 = 
           FIND(
                
                UPPER('Table'[Keyword])
                ,UPPER('Table'[Company]),,0
               )
          

C2 = IF('Table'[Hasvalue]>0&&'Table'[Company]='Table'[Sub],1,2)

Step 4

use this formula to get result table

Table 2 = CALCULATETABLE(SUMMARIZE('Table','Table'[Company],'Table'[Value],'Table'[Is this company a metals company],'Table'[Hasvalue],'Table'[Sub],'Table'[Count],'Table'[C1],'Table'[Index]),'Table'[C2]=2)

Result:

here is pbix, please try it.

https://www.dropbox.com/s/8bqgqaacokqmyrw/Finding%20the%20amount%20of%20values%20in%20table%201%20th...

Best Regards,

Lin

[esiniapkina]

@v-lili6-msft million of thanks! it worked out

simply genious solution