DAX function to count matched text values among rows

[cz37]

Hi,

I'm new to Power BI and hope someone could help me on this! Thank you for your help in advance!

I have a table with ItemName and Category as shown below. I want to create a slicer on ItemName for users to select and an output table shows the number of categories that can be found in the category of selected ItemName for the rest of ItemName. 

ItemName	Category
Apple	red, sweet, juicy
Grape	purple, green, sweet
Banana	yellow, curved
Orange	sweet, juicy

Example: If a user selects "Apple" from the slicer of ItemName, the output table would be shown as below. The categories with underline are the category names found in the category of Apple (i.e. red, sweet, juicy). For example, Grape only has sweet category matched with one of the category of Apple so the number of overlapped category is 1.

ItemName	Category	Number of Overlapped Category
Grape	purple, green, sweet	1
Banana	yellow, curved	0
Orange	sweet, juicy	2

Thanks again!

[Jihwan_Kim · Accepted solution]

Hi,

I suggest having an additional table like below for creating a slicer.

Please check the below picture and the attached pbix file.

It is for creating measures.

Category measure: = 
IF (
    HASONEVALUE ( 'Item'[ItemName] ),
    MAXX (
        FILTER ( 'Item', NOT ( 'Item'[ItemName] IN DISTINCT ( Category[ItemName] ) ) ),
        'Item'[Category]
    )
)

Number of overlapped category: = 
VAR _selectedcategory =
    DISTINCT ( Category[Category] )
VAR _currentrowitemnamecategory =
    SUMMARIZE (
        FILTER ( ALL ( Category ), Category[ItemName] = MAX ( 'Item'[ItemName] ) ),
        Category[Category]
    )
RETURN
    IF (
        ISFILTERED ( Category[ItemName] ) && NOT ISBLANK ( [Category measure:] ),
        COUNTROWS ( INTERSECT ( _selectedcategory, _currentrowitemnamecategory ) )
    )

[cz37]

It works! Thank you!!