Create column with distinct count on category level

[vyacheslavg]

Dear colleagues, 

Could you please advise how to create a column with distinct count on a category level.

The data is quite simple.

product id	part
1	red
2	green
2	green
3	blue
3	black

The result should be 

product id	part	distinct count of parts on a product level
1	red	1
2	green	1
2	green	1
3	blue	2
3	black	2

I tried to play with 

Count of version total for product =

CALCULATE(DISTINCTCOUNT('Table1'[version]), ALL('Table1'[product]))

but it returns the value only on a row level.

It is very simple to create a matrix with this result, but I need these values in the full table with count on row level, because it will be a part of further calculation.

Both DAX or Power Query approaches will do, thanks a lot.

[Ashish_Mathur]

Hi,

This calculated column formula works as well

=CALCULATE(DISTINCTCOUNT(Table1[part]),FILTER(Table1,Table1[product id]=EARLIER(Table1[product id])))

Hope this helps.

[AlB · Accepted solution]

Hi @vyacheslavg

Try this for your new calculated column (close to what you already had):

NewColumn=
CALCULATE (
    DISTINCTCOUNT ( 'Table1'[version] ),
    ALLEXCEPT ( 'Table1', 'Table1'[product id] )
)

[vyacheslavg]

Thanks for the solution, and even I was "close", yay.