Forum Discussion

grayscg's avatar
grayscg
Advocate I
3 years ago
Solved

DAX Count Question

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. 

Need Help
  • parry2k's avatar
    parry2k
    3 years ago

    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  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.

7 Replies

  • davehus's avatar
    davehus
    Memorable Member
    3 years ago

    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))

     

    • grayscg's avatar
      grayscg
      Advocate I
      3 years ago

      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. 

      • davehus's avatar
        davehus
        Memorable Member
        3 years ago

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

         

  • parry2k's avatar
    parry2k
    Super User
    3 years ago

    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  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.

    • grayscg's avatar
      grayscg
      Advocate I
      3 years ago

      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. 

  • parry2k's avatar
    parry2k
    Super User
    3 years ago

    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  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.