Count number of matched words between two columns

[wsMac78AZ]

Hi,

I have 4 columns in my table and I would like to count the number of alphabets in column B that match column D and then produce the result in column E. 

For example, in ticket 1001, all four alphabets matched the alphabets in column D and the total count is "4".

In ticket 1002 only the alphabet "K" matched that in column D hence the count is "1".

I would appreciate any help in solving this problem with PowerBI.

Thanking you in advance.

[v-shex-msft · Accepted solution]

HI @wsMac78AZ,

You can create a calculated column with PATH and GENERATESERIES function to expand the text items, then you can simply use INTERSECT function to compare two lists of items and get the counts.

Item Count =
VAR _pathA =
    SUBSTITUTE ( 'Table'[Order], ",", "|" )
VAR listA =
    SELECTCOLUMNS (
        GENERATESERIES ( 1, PATHLENGTH ( _pathA ), 1 ),
        "Item", PATHITEM ( _pathA, [Value] )
    )
VAR _pathB =
    SUBSTITUTE ( 'Table'[Selection], ",", "|" )
VAR listB =
    SELECTCOLUMNS (
        GENERATESERIES ( 1, PATHLENGTH ( _pathB ), 1 ),
        "Item", PATHITEM ( _pathB, [Value] )
    )
RETURN
    COUNTROWS ( DISTINCT ( INTERSECT ( listA, listB ) ) )

Reference links:

PATHLENGTH function (DAX) - DAX | Microsoft Docs

PATHITEM function (DAX) - DAX | Microsoft Docs

GENERATESERIES function - DAX | Microsoft Docs

Regards,

Xiaoxin Sheng

[wsMac78AZ]

Xiaoxin Sheng,

Many thanks for your assistance. It works for me.

Regards,