Reply
AthabascaDave
Regular Visitor
Partially syndicated - Outbound

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:

 

UsernameAward
BobAchievement 1
BobAchievement 2
BobAchievement 3
NancyAchievement 1
NancyAchievement 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.

1 REPLY 1
vicky_
Super User
Super User

Syndicated - Outbound

vicky__0-1701207960031.png

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

vicky__1-1701208926428.png

 

avatar user

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)