Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count DAX

Hi Community,

I have the following table:

Project IDProject Sub IDValue
100100A10
100100B20
100100B30
101101A40
102102A60
102102B80
102102C90
102102C35
102102C42

I want to create a measure that distinct counts the number of project id repeated in the table, so the following output will be like this:

Project Sub IDCount Measure
100A2
100B2
101A1
102A3
102B3
102C3

Any directions or thought would be appreciated!

Edit: Updated the output of the orignal as I put in the wrong output

Need Help
Show and Tell
Tutorial Requests
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Anonymous 

    please try to create a measure

    Measure = CALCULATE(DISTINCTCOUNT('Table'[Project Sub ID]),FILTER(all('Table'),'Table'[Project ID]=max('Table'[Project ID])))

    pls see the attachment below

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Based on what I got,

     

    You can create a measure

    = count(Table[Value])

    and disply  it with project and sub project id in visual

     

    or create a table

    Summarize(Table, Table[Project], Table[Sub Project], "Count", count(Table[Value]))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak,

      Thank you for your response. Sorry I put in the wrong output and requirement, I updated so it will do distinct count the number of project id repeated for each project sub id. What should I do in this cases instead?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    Is this the result you want ?

    Through grouping 'Table'[Project Sub ID] and then count the column rows .

    count = CALCULATE(COUNTA('Table'[Project Sub ID]),ALLEXCEPT('Table','Table'[Project Sub ID]))

     

    Best Regards

    Community Support Team _ Ailsa Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you for your response, sorry I should have clarified more. What I am trying to do is to count the number of subcategories in each project ID and assign that value to each Sub ID. 100 has 2 distinct categories (102A and 102B), so both 102A and 102B will have 2 as value. 103 has 3 distinct categories (103A, 103B, 103C) so 103A, 103B, 103C will have the 3 as value. 

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        5 years ago

        Anonymous 

        please try to create a measure

        Measure = CALCULATE(DISTINCTCOUNT('Table'[Project Sub ID]),FILTER(all('Table'),'Table'[Project ID]=max('Table'[Project ID])))

        pls see the attachment below