Measure to give a total count of how many names occur in a register more than 5 times

[TFTF_BI]

Hi, I am trying to work out if we can create a single measure that will tell me the ammount of people in a list that have appeared more than 5 times. 

So if Bob appears 7 times in [TableA'column 1'] and James and John appear 4 times in [TableA'column 1'] then I want the measure to show '1'. (As Bob is the only name appearing more than 5 times)

Ideally I need this to be flexible to what ever im filtering to on the visual, this is why I want this creating entirly in a measure.

Im probably really over thinking this. 

Any help would be appriated!

[amitchandak]

@TFTF_BI , try like

Assume you have measure
cnt = count([TableA'column 1'])

then create a new measure
GT 5 = Sumx(filter(valeus([TableA'column 1']), [cnt] >5),[cnt])

[TFTF_BI]

Just tried this method, This is very close, I believe its almost there. 

This is now showing me the total count of 7 using the above as its counting bob has (<6) 7 times. How can we ammend this to give the unique count so its counts 1 rather than 7? 

Let me know if this doesnt make sense. Thanks

[v-zhangti · Accepted solution]

Hi, @TFTF_BI 

It's easy, you try using the function DISTINCTCOUNT.

Measure:

Measure = IF(COUNT('Table'[Name])>5,DISTINCTCOUNT('Table'[Name]),BLANK())

This is the relevant document, hope to help you:

https://docs.microsoft.com/dax/distinctcount-function-dax 

Best Regards,

Community Support Team _Charlotte

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