How to create column or new table to show list of stores with 2 or more people with badge?

[bcbuckley13]

Hello and TIA!

I track users that have received a badge by completing learning. Each store is required to have atleast 2 or more people that have received a specific badge. (there are two different badges, but I need to track the Champions badge).

How could I create a new table/column to basically say "Yes" this store has 2+ people that have received this badge?

https://www.dropbox.com/s/j2s9ww5my4ry3pc/Badges_TEST.pbix?dl=0

[lbendlin · Accepted solution]

Here is a calculated column:

TwoPlus = 
var c = CALCULATE(COUNTROWS('Badge Report_test data'),ALLEXCEPT('Badge Report_test data','Badge Report_test data'[Store ID]),'Badge Report_test data'[Badge Name]="PH Express Champion")
return if(c>1,"yes","no")

your sample data only had one store ID so you may want to validate that.

[lbendlin]

TwoPlus = 
var c = CALCULATE(COUNTROWS('Badge Report_test data'),ALLEXCEPT('Badge Report_test data','Badge Report_test data'[Store ID]),'Badge Report_test data'[Badge Name]="PH Express Champion")
return if(c>1,1,0)

Do a sum, not a count.

[bcbuckley13]

@lbendlin  Is there a way to get a count of "Yes" from that column that you created? I've tried all Count measures and none of them work.

[lbendlin · Accepted solution]

it's a custom column in DAX, not in Power Query

[bcbuckley13]

You Rock!!! Thanks.

[lbendlin · Accepted solution]

Here is a calculated column:

TwoPlus = 
var c = CALCULATE(COUNTROWS('Badge Report_test data'),ALLEXCEPT('Badge Report_test data','Badge Report_test data'[Store ID]),'Badge Report_test data'[Badge Name]="PH Express Champion")
return if(c>1,"yes","no")

your sample data only had one store ID so you may want to validate that.

[bcbuckley13]

@lbendlin  I'm getting this error.

[lbendlin]

Please provide sample data in usable format (not as a picture) and show the expected outcome.

[bcbuckley13]

@lbendlinI've added a sample file in Dropbox. My goal is to be able to see a list of stores that have at least 2 people that have received the Champion badge. This could be in the form of just highlighting these stores.

https://www.dropbox.com/s/j2s9ww5my4ry3pc/Badges_TEST.pbix?dl=0