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: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
422 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
356 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
342 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
357 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
345 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
382 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
379 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
371 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
360 Views
0

Helpful resources

Announcements
Europe Fabric Conference

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

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

View All