cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.