count of matching words of two text columns in power bi

donald smith 0
Viewed 86

i have a table , which has two columns id and text. This is what data i have:

id  text
765 hi how are you
876 John made $57.
743 apple is my favourite fruit.
435 mango is not my favorite fruit.
892 this is my favourite movie

have input column as slicer,in which i can choose any one id.for eg: 743

-765
-876
-**743**
-435
-892

I need the output, in which it would show the id ,text,number of words matching of the selected id from slicer with all the other ids:

id  text                             matching words
743 apple is my favourite fruit.       5
765 hi how are you                     0
876 John made $57.                     0
435 mango is not my favorite fruit.    4
892 this is my favourite movie         3
dax powerbi-desktop powerbi
1 Answers

OK, I have a solution but I'm not convinced it is the most elegant.

enter image description here

Thanks to the following resource for a text.split function in DAX https://www.excelnaccess.com/text-split-using-dax/.

Create a base table named Table as follows:

enter image description here

Create 2 calculated tables with the following code.

New Table1 = 
VAR myvalues =
     ADDCOLUMNS ( 'Table', "Paths", TRIM( SUBSTITUTE ( SUBSTITUTE( [text],".","") , " ", "|" ) ))

RETURN 
SELECTCOLUMNS (
        GENERATE (
            myvalues,
            ADDCOLUMNS (
                GENERATESERIES ( 1, PATHLENGTH ( [Paths] ) ),
                "@word", PATHITEM ( [Paths], [Value], TEXT )
            )
        ),
        "id", [id],
        "word", [@word],
        "text", [text]

    )
New Table2 = 
VAR myvalues =
    ADDCOLUMNS ( 'Table', "Paths", TRIM( SUBSTITUTE ( SUBSTITUTE( [text],".","") , " ", "|" ) ))

RETURN 
SELECTCOLUMNS (
        GENERATE (
            myvalues,
            ADDCOLUMNS (
                GENERATESERIES ( 1, PATHLENGTH ( [Paths] ) ),
                "@word", PATHITEM ( [Paths], [Value], TEXT )
            )
        ),
        "id", [id],
        "word", [@word],
        "text", [text]

    )

Drag New Table 1 [id] to a slicer

Drag New Table 2 [id] and [text] to a table.

Create a measure as follows and drag it into the table.

Matching Words = IF(ISFILTERED('New Table1'[id]), COUNTROWS('New Table2')+0, 0)

Create a relationship between word and word as follows:

enter image description here

That should be everything.

userjpYe10
Related