Forum Discussion
Anonymous
3 years agoNot applicable
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 ItemNam...
- 3 years ago
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 ) ) )
Jihwan_Kim
Super User
3 years agoHi,
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 ) )
)
Anonymous
3 years agoNot applicable
It works! Thank you!!