unique count of filter text

[ap12]

Hello,

I am looking to create a measure that shows unique count for each name. We have a column names but as the field is multi select it combines multi names into one text value. 

ex. column value1 = Name1; Name2; Name3

ex. column value2 = Name1; Name3

What I want to see is how many rows have Name1 or Name2 or etc.

Name1 = 2

Name2 = 1

Name3 = 2

[v-xiaotang · Accepted solution]

Hi @ap12 

It would be easy if you have a name_table with all the names:

Then, create a measure:

countrows =
VAR _name =
    MAX ( 'name table'[name] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        CONTAINSSTRING ( 'Table'[column name], _name )
)

you will get the following:

Hope this helps.

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @ap12 ,

I have made a similar approach but instead of a count I matched the words with found not found, making some adjustments I was abble to get the final result.

For this you need to have an additional table with all the names then add the following measure:

Name Count =
// Character that split phrase into words
VAR SplitByCharacter = "; " // Temporary table that splits selected phrase into words
VAR Words_table_column_1 =
    ADDCOLUMNS (
        GENERATE (
            SELECTCOLUMNS ( ALLSELECTED ( Base[Column1] ), "Find_Text", Base[Column1] ),
            VAR TokenCount =
                PATHLENGTH ( SUBSTITUTE ( [Find_Text], SplitByCharacter, "|" ) )
            RETURN
                GENERATESERIES ( 1, TokenCount )
        ),
        "Word", PATHITEM ( SUBSTITUTE ( [Find_Text], SplitByCharacter, "|" ), [Value] )
    )
VAR Words_table_column_2 =
    ADDCOLUMNS (
        GENERATE (
            SELECTCOLUMNS ( ALLSELECTED ( Base[Column2] ), "Find_Text", Base[Column2] ),
            VAR TokenCount =
                PATHLENGTH ( SUBSTITUTE ( [Find_Text], SplitByCharacter, "|" ) )
            RETURN
                GENERATESERIES ( 1, TokenCount )
        ),
        "Word", PATHITEM ( SUBSTITUTE ( [Find_Text], SplitByCharacter, "|" ), [Value] )
    )
RETURN
    SUMX (
        Words_table_column_1,
        FIND ( UPPER ( [Word] ), UPPER ( SELECTEDVALUE ( 'Table'[Names] ) ),, 0 )
    )
        + SUMX (
            Words_table_column_2,
            FIND ( UPPER ( [Word] ), UPPER ( SELECTEDVALUE ( 'Table'[Names] ) ),, 0 )
        )

Then setup you visualization with the names from the names tables and this measure:

This solution was created using has reference the links below:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Find-Words-on-sentence-from-another-selected...

DAX : Pivot Text into a List of Words 

DAX – “CONTAINSX” – Finding if a value in table 1 has a matching value in table 2 

Check PBIX file attach.