Sum non-repeating values

[Syndicate_Admin]

Good afternoon Community, I hope you are well.

I have a report that needs a query whether or not a field contains a piece of text, example:

I have this table with the values I want to look up:

On the other hand I have a table of several with fields where I want to look up the values of the table above:

Once I look up the text of the first table in the second table the number of the second field of the first table should be added, what I did to achieve this was the following:

I made a Crossjoin of both tables so that n catidad of records were created for each key, so I could consult how many words each comment has since they can have more than one and once that is consulted, I assign the score in a new column of the crossjoin with the following formula:

IF(CONTAINSSTRING('crossjoin'[Comments],'crossjoin'[Key]),'crossjoin'[Score2],0)

example:

This in order that I can compare the texts since I did not find another way to generate it (If you have any idea is welcome), once having this I am generating the necessary fields to create a pareto diagram that would be the frequency and the percentage, but at the time of doing the calculation for obvious reasons it brings me the values of all the cells since I calculate it in this way :

(SUMX(FILTER('Table1',CONTAINSSTRING('Crossjoin'[comments],'Crossjoin'[Key])),table1(Score)))

I want to do that calculation but that only bring me the unique value of all the repeated, it would be like a distinctive sum, because the score is repeated in a general way and I can put it as an average field in the trabla but if I want to apply filters it stays fixed, it does not filter anything and try to do it with measures when converting the field into average but the milesimas do not come out why and the bars although have 0 appear by decimal places.

Could someone who has some free time support me with this?

Thanks in advance, Greetings.

[amitchandak]

@Syndicate_Admin , based on what I got so far.

Create a new column in table 2

maxx(filter(Tabla1, search(Tabla1[clave], Tabla2[Commentarios],0,1)>0 ),Tabla1[score])

or like

sumx(filter(Tabla1, search(Tabla1[clave], Tabla2[Commentarios],0,1)>0 ),Tabla1[score])

[Syndicate_Admin]

Good morning , I hope you are well.

Try to do it as in your comments, but it throws me an error :

An argument of function 'SEARCH' has the wrong data type or has an invalid value.

Greetings and thanks in advance.