Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
aakif_aslam
Helper I
Helper I

Sum of Count(column) > 2 in a measure

Hello to all,

aakif_aslam_0-1667920591030.png

 

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 

 

 

1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

Hi, @aakif_aslam 

Here are the steps you can refer to :

(1)This is my test data:

vyueyunzhmsft_0-1667958565149.png

(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:

vyueyunzhmsft_1-1667958620227.png

 

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

View solution in original post

3 REPLIES 3
v-yueyunzh-msft
Community Support
Community Support

Hi, @aakif_aslam 

Here are the steps you can refer to :

(1)This is my test data:

vyueyunzhmsft_0-1667958565149.png

(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:

vyueyunzhmsft_1-1667958620227.png

 

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
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
Shaurya
Memorable Member
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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors