- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

How to aggregate the number of times a value appears in the table
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:
- Determine if a user appears in the table three or more times.
- 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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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))

Helpful resources
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |