cancel
Showing results for
Did you mean:
Helper II

## Create column with distinct count on category level

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III

## Re: Create column with distinct count on category level

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

```NewColumn=
CALCULATE (
DISTINCTCOUNT ( 'Table1'[version] ),
ALLEXCEPT ( 'Table1', 'Table1'[product id] )
)```
3 REPLIES 3
Highlighted
Super User III

## Re: Create column with distinct count on category level

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

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

## Re: Create column with distinct count on category level

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

Super User IV

## Re: Create column with distinct count on category level

Hi,

This calculated column formula works as well

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors