cancel
Showing results for
Did you mean:  Helper I

## Sum of Count(column) > 2 in a measure

Hello to all, As you see in the above snapshot -

Card show the overall count of Person (Names are repeated), I'm trying to achieve the total count of person with person repeating count is greater than or equal to 2. Based on filter it easy to get the value in the table, I need help in getting this value in a measure.

The data is sensitive hence I have purposely hidden the names in the table.

1 ACCEPTED SOLUTION  Community Support

Hi, @aakif_aslam

Here are the steps you can refer to :

(1)This is my test data: (2)We can create a measure like this:

``````Count >2 = var _t =FILTER( SUMMARIZE('Table','Table'[Person],"Count",COUNT('Table'[Person])) , [Count]>2)
return
COUNTROWS(_t)``````

(3)Then we can meet your need ,the result is as follows: Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

3 REPLIES 3  Community Support

Hi, @aakif_aslam

Here are the steps you can refer to :

(1)This is my test data: (2)We can create a measure like this:

``````Count >2 = var _t =FILTER( SUMMARIZE('Table','Table'[Person],"Count",COUNT('Table'[Person])) , [Count]>2)
return
COUNTROWS(_t)``````

(3)Then we can meet your need ,the result is as follows: Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly  Super User

Hi,

Write these measures

Count = countrows(Data)

Measure1 = SUMX(filter(values(Data[Person]),[count]>=2),[count])

Drag Measure1 to a card visual.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com  Memorable Member

Hi @aakif_aslam,

You can create a count column in DAX using:

``Count = SUMX('Table',IF('Table'[Person]=EARLIER('Table'[Person]),1,0))``

And then use this formula for your measure:

``Number of People = CALCULATE(DISTINCTCOUNT('Table'[Person]),FILTER('Table','Table'[Count]>=2))``

Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: How to Export Telemetry Data from Azure IoT Central into Power BI  