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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KeepCalm007
Helper I
Helper I

DAX Function for COUNTIF and/or CALCULATE

Hi power bi community

 

I am trying to create a calculation to tell me how many people have their names appear 5 or more times in the 'ALPHA-SORT' column, and distinctly-count that individual if they met that criteria (appeared 5 or more times in my report)

 

Capture1.PNG

 

This is the calculation I tried, but it didn't calculate the correct people to show when I try to graph the values

Capture2.PNG

 

my aim is to able to sort individuals by DEPTNAME as a starting point.

Can someone advise what I must have left out in my calculation in order for it to work?

 

Thanks,

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @KeepCalm007

 

I would use either of these two measures:

 

5days =
CALCULATE (
    DISTINCTCOUNT ( Table1[ALPHA_SORT] ),
    FILTER ( VALUES ( Table1[ALPHA_SORT] ), CALCULATE ( COUNTROWS ( Table1 ) ) > 4 )
)
5days =
COUNTROWS (
    FILTER ( VALUES ( Table1[ALPHA_SORT] ), CALCULATE ( COUNTROWS ( Table1 ) ) > 4 )
)

These measures filter the values of ALPHA_SORT to those that appear in more than 4 rows.

 

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

4 REPLIES 4
OwenAuger
Super User
Super User

Hi @KeepCalm007

 

I would use either of these two measures:

 

5days =
CALCULATE (
    DISTINCTCOUNT ( Table1[ALPHA_SORT] ),
    FILTER ( VALUES ( Table1[ALPHA_SORT] ), CALCULATE ( COUNTROWS ( Table1 ) ) > 4 )
)
5days =
COUNTROWS (
    FILTER ( VALUES ( Table1[ALPHA_SORT] ), CALCULATE ( COUNTROWS ( Table1 ) ) > 4 )
)

These measures filter the values of ALPHA_SORT to those that appear in more than 4 rows.

 

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

@OwenAuger Thank you for your help Owen. I didn't think to use countrows. Are there any resources you can recommend me for improving my formulation of calculations? Smiley LOL

Ashish_Mathur
Super User
Super User

Hi,

 

In the Filter function, replace Table1 with VALUES(Table1[Alphasort])

 

Does this work?

 

 

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur No it did not work, but thank you for your help

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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