Measure DAX Formula - to calculate number of instances a word appear in another column

[Anonymous]

Hi 

I am looking to find a formula, to calculate a measure. 

Scenario

1. I have 2 columns - "Licensee" and "Advisers"
2. Multiple Advisers can belong to the same licensee

Ask

how to calculate a measure to That I can use in "Card" visual,   to show case number of Licensee with more than 20 advisers only.

[tamerj1]

Hi @Anonymous 

a small sample data would be greatly useful to support with accurate code. 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure without seeing your data model, however, I tried to create a sample pbix file like below.

Please check the attached file and the below measure.

Licensee count (more than 20 advisers only) measure: =
VAR _newtable =
    ADDCOLUMNS (
        VALUES ( Data[Licensee] ),
        "@countadvisers", CALCULATE ( COUNTROWS ( VALUES ( Data[Advisers] ) ) )
    )
VAR _filternewtable =
    FILTER ( _newtable, [@countadvisers] > 20 )
RETURN
    COUNTROWS ( _filternewtable )

[umeshbanga]

Thanks mate, it worked. How do I mark this as a solution?