Skip to main content
cancel
Showing results for 
Search instead 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

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.