Count Values That Repeat in Column

[sparkplug93751]

Hello,

I have two column - name and employee

I need to count each time name appears more than 3 times ie they have >3 employees in a measure.

I have it down to a measure that counts rows then in the matrix it shows how many employees everyone has but I can't then count the number of names.

See attached data: https://tuprd-my.sharepoint.com/:x:/g/personal/tug67925_temple_edu/ET7ftu-eRp5MpxDnrwZAdg4BUI4LUppNd...

The measure should = 2 since Sam and Tim both have over 3 employees. I'm not looking for how many employees. Just the number of names with more than 3

[nandukrishnavs · Accepted solution]

@sparkplug93751 

Try this

Measure = 
var _gp= SUMMARIZE('Table','Table'[Name],"_count",COUNT('Table'[Name]))
var result= COUNTROWS(FILTER(_gp,[_count]>3))
return result

Note: performance-wise this may not be a good solution. You can do the same thing in Query Editor.

Edit Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsnMVdJRMlSK1YGxjZDYxkhsEzDbJz89MQ/IM0XhmYF5wYkgdeZIbAsktiUS29AAmQO0PRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #"Employee #" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Employee #", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Count] > 3)
in
    #"Filtered Rows"

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂