Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
grayscg
Helper I
Helper I

DAX Count Question

‎09-22-2022 07:35 AM

Hey so im fairly new to working with dax and I have what I think is a pretty simple question. I have tried searching around but cant find what I need.

 

I want a measure to count when the frequecy of something is above a certain number. For example, in the simple table of:

 

CustomerID
A1

B2
A1

A1

B2

C3

 

I was a measure to count how many customer IDs appear at least 2 times. So the return would be 2. 

 

A simple calculate function doesnt seem to work. I have tried to use a combination of calculate and filter, but have not been able to produce the expected result. Any help would be appreciated. 

Labels:
Message 1 of 8
954 Views
1 ACCEPTED SOLUTION
parry2k
Super User
Super User

‎09-22-2022 10:27 AM

@grayscg maybe this measure will do it:

 

Count = 
VAR __checkCount = 2
RETURN
SUMX ( 
    SUMMARIZE ( 
        Test, 
        Test[CustomerID], 
        "@Count", COUNTROWS ( Test ) 
    ), 
    IF ( [@Count] >= __checkCount, 1 ) 
)

 

✨ Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

Message 6 of 8
888 Views
7 REPLIES 7
parry2k
Super User
Super User

‎09-22-2022 11:34 AM

@grayscg I can try to explain what is going on but you can surely explore it further

 

SUMMARIZE -> is creating a table, basically getting count by customer id
SUMX -> is outer function iterating over a table produced by SUMMARIZE  and then checking if count >= 2 then return 1 else blank () and it summing up all the 1's

 

I hope this detail is helpful but ofcourse you need to learn more about when and how to use these functions. Good luck!

 

✨ Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Message 8 of 8
874 Views
parry2k
Super User
Super User

‎09-22-2022 10:27 AM

@grayscg maybe this measure will do it:

 

Count = 
VAR __checkCount = 2
RETURN
SUMX ( 
    SUMMARIZE ( 
        Test, 
        Test[CustomerID], 
        "@Count", COUNTROWS ( Test ) 
    ), 
    IF ( [@Count] >= __checkCount, 1 ) 
)

 

✨ Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Message 6 of 8
889 Views
grayscg
Helper I
Helper I
In response to parry2k

‎09-22-2022 11:07 AM

This works! Thank you! I do not understand enough about dax to know how it works, but this gives me some new functions to study. 

Message 7 of 8
877 Views
0
davehus
Memorable Member
Memorable Member

‎09-22-2022 08:25 AM

Hi @grayscg ,

 

Try the measure below.

 

Replace CustomerCount with your own table reference.

Hope this helps.

Did I help you today? Please accept my solution and hit the Kudos button.

 

 

Customer Count = CALCULATE(DISTINCTCOUNT(CustomerCount[CustomerID]), FILTER(CustomerCount,CALCULATE(COUNTA(CustomerCount[CustomerID]))>1))

 

Message 2 of 8
914 Views
grayscg
Helper I
Helper I
In response to davehus

‎09-22-2022 08:46 AM

This feels close. Right now that is returning a blank, where as when I flip around the > sign, it returns all the records. Something with the Distinct/Count is off. Attempting to tweak it now. 

Message 3 of 8
911 Views
0
davehus
Memorable Member
Memorable Member
In response to grayscg

‎09-22-2022 08:59 AM

If you have some anonymous data, send it to me and I'll have a look for you.

 

Message 4 of 8
903 Views
0
grayscg
Helper I
Helper I
In response to davehus

‎09-22-2022 10:18 AM

What I dont understand is that it works in the example table but not in mine. I took out a sample of just the ID column, and scrambled it, to see if it work work there. It doesn't, it does the same thing where it believes every ID is distinct. Here is the table I am using. 

 

IDNumber

S0003089302
S0003089302
S0003089302
S0003089302
S0003089302
S0003089302
S0003089302
S0003089302
S0003089302
S0003089302
S0003089302
S0003089301
S0003089301
S0003002002
S0003002002
S0003002002
S0003002002
S0003002002
S0003002002
S0003002002
S0003002002
S0003002002
S0003002002
S0003002002
S0003002002
S0003002001
S0003002001
S0003002001
S0003002001
S0003002001
S0003002001
S0003002001
S0003002001
S0003002001
S0002399101
S0002395201
S0002395201
S0002395201
S0002395201
S0002395201
S0002395201
S0002395201
S0002395201
S0002394901
S0002384101
S0002377901
S0002358203
S0002358203
S0002358203
S0002358203
S0002358201
S0002358201
S0002358201
S0002358201
S0002358201
S0002358201
S0002358201
S0002358201
S0002357601
S0002357601
S0002357601
S0002357601
S0002357601
S0002357601
S0002357601
S0002357601
S0002357601
S0002357601
S0002355501
S0002353103
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
S0002353101
Message 5 of 8
892 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All