Count DAX

[Anonymous]

Hi Community,

I have the following table:

Project ID	Project Sub ID	Value
100	100A	10
100	100B	20
100	100B	30
101	101A	40
102	102A	60
102	102B	80
102	102C	90
102	102C	35
102	102C	42

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 ID	Count Measure
100A	2
100B	2
101A	1
102A	3
102B	3
102C	3

Any directions or thought would be appreciated!

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

[v-yetao1-msft]

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]

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 · Accepted solution]

@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

[Anonymous]

Thank you so much for help! Could you also provide me the explaination of how the measure above works so I could use it as a future reference?

[ryan_mayu]

@Anonymous 

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

if we have subject ID, then we can get the corresponding project ID.

FILTER(all table)- remove filters to search the whole table.

then search the project IDs which are the same as the current one.

max or min will return the current project ID

[amitchandak]

@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]

@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?