Forum Discussion
DAX function to count matched text values among rows
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!
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 ) ) )
2 Replies
- Jihwan_Kim
Super User
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 ) ) )