Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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...
  • Jihwan_Kim's avatar
    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 ) )
        )