Sum of Count(column) > 2 in a measure

[aakif_aslam]

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.

@Greg_Deckler 
@amitchandak 

@Shaurya 

@Ashish_Mathur 

[v-yueyunzh-msft · Accepted solution]

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

[Ashish_Mathur]

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.

[Shaurya]

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