How to aggregate the number of times a value appears in the table

[AthabascaDave]

Hi there. I've looked this up extensively but keep finding near solutions and am not experienced enough with Power BI to stick the landing.

I have a table which shows a number of badge awards in a system. I want to produce a simple aggregated number that shows all users who have achieved five or more badges.

The (simplified) version of the data is this:

Username	Award
Bob	Achievement 1
Bob	Achievement 2
Bob	Achievement 3
Nancy	Achievement 1
Nancy	Achievement 2

So in this case Bob has 3 achievements, Nancy has 2.

What I want to do is this:

1. Determine if a user appears in the table three or more times.
2. Count how many users have met that criteria.

So my ideal end visual would just be a simple card that says '40 users have completed all badges'.

I've been playing with calculated columns and measures, but I can't figure out how to aggregate a measure (as I can do a measure which does this count no problem), or can't figure out how to calculate a column which displays the total number of times a value appears in the table.

I'm sure this is relatively simple, but happy to take any suggestions.

[vicky_]

For the first part, if you simply want to find the number of times a person has appeared, then you can simply count the number of times the person's name appears. You can use a visual filter to only show those who meet that condition. This can all be done without DAX.

If you want a DAX solution, then you can try the following:

Awards = 
var award = COUNTROWS('Table') // how many times the name appears in the table
return IF(award > 2, award, BLANK())

to get the awards only if the username has more than 1 award. 

Or to get a measure to say how many total users have completed that award, you can do something like:

users completed awards = 
var helperTable = SUMMARIZECOLUMNS('Table'[Username], "awards", COUNTROWS('Table'))

return COUNTROWS(FILTER(helperTable, [awards] > 2))